Issue in subtotal with Smart filters using user list
I have created a smart filter using the user list. The filters are working fine.
The issue is in the sub-total, is there a way to get the sub-total in an optimized manner.
Currently to get the subtotals right, I need to use user list as a dimension in my module and use an If formula, eg. if the filter is true than take value else 0, but the issue is that I need to include my user dimension which is increasing the size of the module.
Any suggestions if correct subtotals can be calculated based on the filter without using the user dimension in the module.
There is not a great way to do this short of dimensionalizing your module by users.
An alternative could be to create a line item and use it as a selector on the user dimension, and then basically set this up as you have it now, by saying if filter is true then Data else 0, however if your current structure does not work then i doubt this does either.
Without introducing the user dimension here then the data in the modules will remain the same and the subtotals will not be dynamic in the way that you are asking.
Could you use some smaller list as the dimension instead of the user? Something like Position? that way if you had 100 users, but only 10 job positions in the model, then you could make that a dimension and only make the module 10x bigger vs 100x bigger. You could assign this role via Selective access so that the filter is automatically applied, and the subtotals are correct for each user.
Hey @jasonblinn ,
Thanks for the suggestions, I was wondering if we have some @CurrentUser kind of option which I can use in the if formulas instead of using the whole user dimension.
Anyways thanks for your feedback.
This would be really much appreciated, now people will not see the correct totals after filtering, this limits the use of the reports in Anaplan. And makes people move to an BI solution.0
If this kind of issue will be solved somehow easier, I think will be a game-changer.
However, I found it really challenging.
The data cells of the sub-totals from a module are pre-calculated before the module is presented in a dashboard for filtering.
Using the filtering capabilities (normal filters or Quick filters) will only show or hide the cells, but the filtering will not influence the result that is showed in the values of the aggregated cells from a module.
A single cell from a module (base member cell or aggregated cell) can contain one single value and it cannot show for a user one result and for another user, another result, based on the filters applied on the module. This would mean that the totals need to be re-calculated on the client-side and not the Anaplan servers based on the filters made by the user in the dashboard.
This is why it is needed in the module to generate cells that through the formulas to simulate what the filtering capabilities do and generate correct results based on the flags.
In order to generate different cells by users, it is used "Users" list ( as @Yash1 did) or another dummy list - what @jasonblinn suggested as an alternative to generate fewer cells.
These lists need to be added to the other lists of the module that need to be filtered.
Only this way are generated different cells that can have different results by users. Every user will see their Totals, because of the "selective access" setup...but physically the Totals cells are different for every user, and every user can generate his Total based on the "filters" chosen. The "filter" is generated by the boolean flags that are checked by every user using the filter modules.
Unfortunately, I cannot see other solutions in the current situation. 😞
The BI solutions (or Excel) that re-calculate totals based on the user filters are using the client resources to do this.
However, these solutions are not so scalable as Anaplan. . There are limited resources on user computers.
Hope it helps