Skip to content

    • Summary Calculations With the User List

    in Best Practices

    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.

    Tagged:

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Sign In

    Comments

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Sign in or register to get started.
    Sign In

    Quick Links

    Categories