Assistance with cumulative count per month, grouped by 2 values

[Christopher_Lawrence]

I am working on providing a slide that presents metrics based off of the count of users that apps currently have.

The first thing I had created was a stacked pivot chart that displays a cumulative count of all users, stacked by the role name and grouped year/month derived from the value UserCreatedOn. The bars for the dimension holds the variable [UserCreatedOn.autoCalendar.YearMonth], which derives the year/month from the date result of UserCreatedOn. The actual measures/length of the bar are derived from this set analysis:

=rangesum(Above(Count({<SimpleRoleName={'One of the four role names'}>}U.User_ID),0,RowNo()))

This provides me with a stack column graph that displays the cumulative count of users per month (All user profiles created during or before the column month), stacked by role.

I am now attempting to go more granular with the data and provide a table that displays the same thing above (but is now a table for export of the granular data), but is grouped by the environment of the app and the hostname (Desired end result example attached w/excel sheet). However, this is where I run into a wall (unsure if I would need to use a table or pivot table for this as well), but I want the table to display the environment of the app, the app name, the 4 columns present the cumulative count of users (created on or before the last columns year/month result), and the year/month result ( Variable [UserCreatedOn.autoCalendar.YearMonth]).

I take it I would still leverage the previously displayed rangesum formula, but how do I now change that to accommodate for grouping on the environment and the app name? Would this be where I leverage something along the lines of the aggr function? Any suggestions would be greatly appreciated, thanks!

[jbhappysocks · Accepted solution]

Hi

Can you attach a screen shot of what you are getting when you use your calculation in a pivot table please. From what I can see it looks fine, I must be missing something here.

[Christopher_Lawrence · Accepted solution]

I believe I found the issue occurring. I thought the formula was right, so I doubled back on the nuances of the functions I leveraged and realized the Above function can be impacted by the current sorting hierarchy that is set in the sorting module of the properties box. Originally I thought this was derived from the actual sorting of the table. That seemed to have resolved my issue. Thanks for confirming my formula was correct!