Cumulative count with 2 dimensions in a bar chart

[prithviKamath]

Hello.

I am a newbie to Qlik Sense.

I wish to develop a stacked and cumulative bar chart with 2 dimensions.

My first dimension is: MonthName(Date#(JoiningDate))

Second dimension: Country

Measure: RangeSum(above(total Count(distinct employeeID), 0, RowNo(total)))

This gives me a wrong result as employees in blue country are double counted while counting employees belonging to red country.

As we can clearly see from the below image, employees belonging to red country are far less than employees belonging to blue country.

However, I get the right results if I filter out on a single country.

Correct cumulative counts for blue and red country respectively:

I tried the aggr function too, but its gives me a blank viz.

Aggr(RangeSum(above(total Count(distinct employeeID), 0, RowNo(total))), Country, MonthName(Date#(JoiningDate)))

Any help will be greatly appreciated! Thanks!

[sunny_talwar]

First thing, you need to create a new MonthName field in the script like this

MonthName(JoiningDate) as JoiningMonth

Or it would even better if you actually create it like this because this will help you with set analysis if you ever have to use it with JoiningMonth field.

Date(MonthStart(JoiningDate), 'MMM-YYYY') as JoiningMonth

Once you have this field, you can try this

Aggr(
    RangeSum(Above(Count(DISTINCT employeeID), 0, RowNo()))
, Country, JoiningMonth)

[prithviKamath]

Thanks for your time @sunny_talwar . I got the result but with some changes!

I used your second approach to load the JoiningMonth using Date(MonthStart(JoiningDate,'MMM-YYYY')

And then using your measure, I get this.

There seems to be 2 things wrong here. One, its not a cumulative, and second the patient counts are all messed up.

I resolved the cumulative part by adding 'Total' before the count and in rowNo(), but it still gave me wrong results.

Finally, after going though some of your replies to other questions, I have added the (NUMERIC, ASCENDING) to make my measure look like:

Aggr(RangeSum(Above(Count (DISTINCT employeeID), 0, RowNo())), Country, (JoiningMonth,NUMERIC, ASCENDING))

This gives right results!!!

@sunny_talwar, request you to edit your response so that I can select your answer as solution!

Thanks again!

[sunny_talwar · Accepted solution]

I don't think you need TOTAL here... can you try this without total

Aggr(
  RangeSum(Above(
    Count (DISTINCT employeeID)
  , 0, RowNo()))
, Country, (JoiningMonth,NUMERIC, ASCENDING))