Even though the用户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用户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:
用户- formatted as Users, no formula
BooleanLine 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用户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产品sand用户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 (用户) without a top-level member, we will also need a line item identifying the产品list member.
Even though theTargetmodule is only dimensionalized by产品, 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用户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.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
Great news! Maybe someday we will see a similar post about native versions list
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.
