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.
Best Answer
-
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.
1
Answers
-
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
0 -
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!
1 -
Thanks Ryan for helping!
0