Count Filtered result

Hi everyone,

I have two modules, let's say M1 and M2. In M1, the dimensions are Users and Activity (List of activities). In M2, the dimensions are Styles and Time. I have made a filtered (Style filter) which filters styles in M2 according to the selection made by the user in M1. If there is no selection made, M2 shows all the styles.

Now, I want to create a target module where I want to count filtered styles and M2."Boolean 1" true with respect to time. M2 also has a Time filter which filters Styles based on time.

Ex:-

Scenario 1 :- If user select Activity 2 and Activity 3 in M1 then I want to count the number of styles which are filtered by "Style filter" and having M2."Filter 1" true. These are styles which are classified as Activity 2 and Activity 3. "Style filter" filtered them after user made selection in M1.

Scenario 2 :- If user doesn't select any activity then I want to count all the styles for which Boolean 1 is true.

P.S.:- I have attached the basic structure of all three modules with this post.

Screenshot_2022-06-07_at_4_139257.png

Accepted answers

ryan_kohn

Interesting use case! Very curious as to what you are trying to ultimately accomplish.

I was able to mock up an example that I believe meets your requirements. I have published all the outputs to a single Page for visual reference. Both scenarios are outlined below. I have displayed the context for each card in order to help you follow the dimensions for each. None of the modules have subsidiary views.

Scenario 1:

Scenario 2:

For M1 and M2, I followed your mockups. Blueprints are here:

For the calculation that determines whether or not a user has selected any activities, I created a System module per our best practice, as I can see this calculation being used in other places. The calculation uses an ANY aggregation to summarize the Activity selection by User. Note the Applies To only includes Users, as we have aggregated the result.

For the additional filter logic, I created a staging calculation module that breaks up the logic into bite-sized chunks per our best practices -- both to help humans like us follow the logic, as well as help the Anaplan calculate things performantly.

A couple notes:

The Final Filter line item captures the logic across both your scenarios. The "AND" portion returns the 'Boolean 1' result for each user that has No Activities Selected, while the "OR" portion returns the combined Style Filter/Boolean 1 for users that have selected activities.
The Count line item converts this final filter into a numerical value so that we can SUM the result in our final step.

Our last step is to generate the final SUM across the Activity dimension per your requirement.

I hope this meets your needs, but I can elaborate on any portion if you want.

All comments

ryan_kohn

Interesting use case! Very curious as to what you are trying to ultimately accomplish.

Scenario 1:

Scenario 2:

For M1 and M2, I followed your mockups. Blueprints are here:

A couple notes:

The Final Filter line item captures the logic across both your scenarios. The "AND" portion returns the 'Boolean 1' result for each user that has No Activities Selected, while the "OR" portion returns the combined Style Filter/Boolean 1 for users that have selected activities.
The Count line item converts this final filter into a numerical value so that we can SUM the result in our final step.

Our last step is to generate the final SUM across the Activity dimension per your requirement.

I hope this meets your needs, but I can elaborate on any portion if you want.

ShubhamCh

@ryan_kohn

Hi Ryan,

Thanks for the solution. You've also explained it very clearly and simply.

I've understood it but there is one more thing that I'd like to ask. That is, is there any way that I can place that count line item in Module M2 itself? I know that M2 doesn't have Users as a dimension but is it anyhow possible? I posted these scenarios with limited number of styles. The actual model that I am working on has over 50k styles and number of users is also close to 100. So if I make the staging model with Style, Users and Time as dimensions, the cell count exceeds 10millions. I am lil bit concerned about it, so, do you have any suggestions or another method which can keep cell count in check?

Thanks once again!

-Shubham

ryan_kohn

With your requirements as described, you still have a dependency between the Users list and the Count (due to needing to compare against the user's activity selections as part of that logic).

Regarding your general concern on size, cell count should not be your primary concern -- note that model size doesn't actually fall into the PLANS standard.

Don't forget that you have an actual requirement to meet and above all, you need your model to do something valuable for the business. The purpose of the standard is merely to accomplish that in the most optimal way. The above approach is focused on maximizing the aspects of the PLANS standard -- performant, logical, auditable, necessary, and sustainable -- while still meeting the requirement.

Some best practices to note that I paid attention to in my proposed solution:

Separate larger calculations into a Calculation module (M2a), and turn off summary options. See 2.01-10 and 2.03-01).
Breaking up modules to use only the necessary dimensions for the calculations (e.g. splitting out SYS01 from M2a). See 2.01-04, 2.01-12 and 2.01-20.
Avoiding subsidiary views by breaking up the modules. See 2.01-06.
Breaking up the formulas in M2a in order to have the calcuation engine perform more efficiently. See 2.02-18, along with the best practice article Formula Structure for Performance.
Avoiding nested IFs -- note the formula in particular for Final Filter where I instead combined Boolean logic to have a single formula. See 2.02-01.

Note in particular that breaking up formulas (2.02-18) actually increases cell count rather than reduces it. However, what we've learned over time is that this is typically better for performance than reducing cell count.

In summary, I wouldn't worry too much about the cell count unless you are extremely constrained on size for other reasons. Just focus on optimizing where you can!

ShubhamCh

@ryan_kohn

Thanks Ryan for helping!

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…