Register

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:
Picture1.png

 

Picture2.png
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]

Picture3.png

Picture4.png

Changing the user to Stephen, the formula will return False because Stephen has not checked the Boolean.

Picture5.png

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.

Picture6.png

 

 

Picture7.png

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.

Picture8.png

 

Picture9.png

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)

Picture10.png

Abbreviated data:

Picture11.png
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.

Picture12.png

 

Picture13.png

 

Picture14.png

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.

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
Comments

Huge step forward @rob_marshall - thanks for documenting all this!

@rob_marshall Thanks for the detailed explanation!

@rob_marshall Thanks for sharing. 

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 :).

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

 

About the Author
Labels (2)