Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Christopher_Lawrence
Contributor II
Contributor II

Assistance with cumulative count per month, grouped by 2 values

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!

Labels (3)
2 Solutions

Accepted Solutions
jbhappysocks
Creator
Creator

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.

View solution in original post

Christopher_Lawrence
Contributor II
Contributor II
Author

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!

View solution in original post

2 Replies
jbhappysocks
Creator
Creator

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.

View solution in original post

Christopher_Lawrence
Contributor II
Contributor II
Author

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!

View solution in original post