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 formulaBoolean 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.
