Author: Rob Marshall, Director of Architecture and Performance at Anaplan, Operational Excellence Group (OEG).
Prior to the February 2024, users were not allowed to use aggregation functions (SUM, ALL, ANY, AVERAGE, FIRSTNONBLANK, LASTNONBLANK, MAX, MIN, RANK) when having the Users list in a module because the system could not aggregate the numbers to a top level. As of our February 2024 release, that has changed.
Prior to February 2024
First, let’s look at the previous behavior. In the below, I have an Input module with products in the row axis and Users in the column axis (if I were not a Workspace Admin, I would only see my own user ID).
Input Module:
If I wanted to get the total of Product Amounts, I would create a RPT module to sum the Amounts by Product, but I would get the following:
With the February 2024 update, this has changed!!!!
Users displayed under General Lists
The User list is now displayed under the General Lists menu under Contents, like all your other lists.
Highlight Users and click Open, you will have the same options as you do with other lists and can select Tree View, Grid View, Subsets, and Configure.
Clicking Configure, you can now add a Top Level Item. Enter the text you would like to use for the Top Level Item and click Apply.
Now, we can go back to the RPT module (dimensionalized by Product) and enter our SUM function.
Not only does this work for sum, but it works for all aggregation functions.
Subsets on Users
Going back to General Lists and clicking on the Users list, navigate to the Subsets tab. Click Insert and you can now add subsets to the Users list, which will behave the same as subsets defined on other lists.
Once defined, select the Grid View tab where you can define who is part of a certain subset.
*** Note, automation of the user subset definitions via an action is not currently possible, however we wanted to get this functionality (Users with a Top Level Item and User Subsets) out as quickly as we could since it has been one of the most requested enhancements.
In order to use the User Subsets in a module, you can select the subset the same way as you would with regular subsets. Notice, people can belong to more than one subset.
Or if you are already have a module defined and you want to add the subset, click the ellipsis on the right under Applies To in order to see the full list of lists.
The aggregation functions will work the same way with the User Subset.
Going back to our RPT module, we can now add two new line items to get the aggregation totals for the subsets.
No List and/or using Select
With the added Top Level, you can also get sums without having a list defined in the module (helpful for validation purposes) as well as using the Select function.
In conclusion, the use of aggregation functions can now be used with the Users list by adding a Top Level item to the list which is a great new feature.
Video:
