[UPDATED] Summary Calculations with the User List
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:
Comments
-
Much needed feature. Thanks Rob for sharing the update.
0 -
@rob_marshall : thanks a lot for sharing!!!
Really useful and awaited feature to limit the users reporting.
1 -
🎉🎉🎉🎉🎉
0 -
@rob_marshall thanks for the update.
0 -
@rob_marshall This is a massive advancement! Great work here
0 -
Much awaited one! Great work
0 -
@rob_marshall Thanks for the details, really much awaited feature. Now it's time to get rid of all those fake user list in our models 😜
3 -
Really an amazing and much needed feature. This can benefit us by avoiding the dummy user list.
0 -
Amazing to see this feature. Will help a lot where we want only particular users in a module. Can use subsets and assign to it. Plus summation and all will be possible. 🙌🥳
0 -
@rob_marshall - really great stuff. have a couple of questions. 1) the ability to see all users for non-workspace admins is something that we get asked about quite a bit particularly around access setup - when might that be resolved and 2) will end users be able to see totals, etc if they can't see other users in the user list?
0 -
Looks like it works pretty much the same as any other list, @andrewtye I would expect it to feature selective access as well?
0 -
@rob_marshall this is wonderful, and brilliantly explained and demonstrated - thank you so much.
Like @andrewtye I get asked about user management for non-admins to see and have had to build a User Management model to compensate. I look forward to future developments with this - thank you.
0 -
Very excited to be finally able to subset users! 😊
2 -
Good questions…On the first one, no, I do not know when that will be updated or what the status of that actually is. On number 2, non WSA's will not be able to see the Total of Users from an input perspective, but they will from a report perspective (getting the data from the input and reporting it out without Users as a dimension).
5 -
thanks @rob_marshall - good to know about #2 otherwise would have to have all sorts of maddening workarounds to show those.
1 -
This feature is going to make a huge difference. Thanks for passing that along @rob_marshall
0 -
@rob_marshall You've done it again, yet another brilliant post to help us capitalize of these new enhancements.
The ability to manipulate the user list for calculations is something I have often heard in the wish list of my peers. Very cool to see this coming to fruition.
2 -
Congratulations for this new and useful feature @rob_marshall !
Especially the subset one that will allow us to reduce space usage on calculations done by User when not all users are necessary.
However, when we have User Management features developed in our solutions, the ability to define those subsets by using an import action will be a must have… Hope that this capacity will come soon enough.
More, I hope that you will be able to give use access to Selective Access rights by formula (I know it may be complicated, but it would be a huge benefit in many solutions)
Cheers!
0 -
Awesome update! No more Fake Users needed.
1 -
Wahoo!!!!! Thanks, @rob_marshall, for the clear presentation.
0 -
@andrewtye : to make non-Workspace admins "see" ALL the users, it can be solved by using a fake-Users List and LOOKUP from the modules with native Users lists.
But this is still a workaround as @Becky noted.
It is needed to make sure that the Fake Users List is updated from native users and it is needed to duplicate the modules for which you need to show all the users.
I think this should be OK as the data needed to show all the users for non-WSA should not be on big modules.
1 -
It would be nice if we can fetch the first name, last name of a user and somehow get the selective access details in a module with some pre-defined functions or formulas. That would also be another game changer like this users subset and gives more flexibility working with native users list. For now we can export the user info and load it using export and import actions and automate it using integration.
Is this something which is Anaplan currently working on and is on pipeline?
5 -
Awesome update, thanks @rob_marshall for sharing !
0 -
Awesome update, subset will be very useful
0 -
@rob_marshall Awesome!!! much awaited and needed feature. Great going team Anaplan.
0 -
This feature is very timely and necessary to manage users. Now we can make several adjustments in our models. Thanks!
1 -
That is fantastic!
0 -
This is very precise and concise, very powerfully detailed. Thanks @rob_marshall
0 -
Thank you so much for detailed explanation @rob_marshall
0
