Summary Calculations With the User List
Even though the Users list does not have a top member assigned to it, with the release on April 24, 2021, model builders will be able to use the following summary functions with the Users List: SUM, LOOKUP, ISFIRSTOCCURRENCE, CUMULATE, ALL, ANY, AVERAGE, FIRSTNONBLANK, LASTNONBLANK, MAX, MIN, RANK.
Lookup
First, let’s look at the Lookup function and see how that works.
In this use case, I have a module dimensionalized by Users with one line item, a boolean, and I want to return that value in a different module.
Source Module:
In my target I have a simple module with two line items, not dimensionalized by any list:
Users - formatted as Users, no formula
Boolean Line Item - formatted as a boolean with the formula being:
Module B.Boolean Line Item[LOOKUP: Users]
Changing the user to Stephen, the formula will return False because Stephen has not checked the Boolean.
Summary Functions
Since the Users list does not have a top-level member, you will need to do the summations a bit differently as in specifying the list (in the source module) that you want to sum on. This is the current functionality when using summary functions on lists not having a top-level member, but we normally just add the top-level member to the source list.
The source module is dimensionalized by Products and Users and includes several line items formatted as numbers, Booleans, and list members. In this case, since we are using the summary functions with a list (Users) without a top-level member, we will also need a line item identifying the Product list member.
Even though the Target module is only dimensionalized by Product, we are able to use summary functions because we have defined the product in a line item in the source module.
Real-World Example
Use Case: I need to report on the total number of hours and role users spent for all activities.
Solution:
Transactional Data:
Lists: Users, Activities, Time (Day)
Line Items: Hours (number formatted), Role (list formatted)
Abbreviated data:
Reporting Module
Lists:Activities, Roles, Time (Month)
Line items: Hours formatted as number with the formula:
Time Entry.Hours[SUM: Time Entry.Role]
This formula works because the module is formatted at the Month level, but the line item is Time formatted at the Day level. This is nice little trick in going from Day or Week level source data to a target module of Month while summing on a line item.
Conclusion
In conclusion, calculation functions for the Users List are adding value by allowing users to query on user specific values; but pay specific attention to your user management process, as users removed from the model (‘No Access’ or deleted from the workspace) will also have the data associated with them removed.
Comments
-
Huge step forward @rob_marshall - thanks for documenting all this!
0 -
@rob_marshall Thanks for the detailed explanation!
0 -
@rob_marshall Thanks for sharing.
0 -
Thanks for sharing!
Finally, the Users list behaves like the "normal" lists and it is not needed the workaround to use the Users dummy lists
.0 -
Great news! Maybe someday we will see a similar post about native versions list 🙂
0 -
In the past, we had to create a separate user list and number of actions to do the same modelling but now no more...
Awesome functionality we've been waiting for. Great stuff. thanks. 😁
0 -
Awesome enhancement! But one thing is still open which is the currentuser() functionality. In your 1st example of using lookup, where instead of manually selecting the user value if the system could allow us to bring the current user data, that would be really great addition to the user dimension.
0 -
@JaredDolich @pallavi6.gupta @ashish.banka @alexpavel @AntonMineev @michael_park @kavinkumar
Since you folks had comments on the above, I wanted to alert you to a new feature and document that was released this morning and can be found here:
3
