Rangesum cumulative with 2 dimensions

[reivax31]

HI Qlikers,

I'm bulding a chart with cumulative sum and 2 dimensions. I found a lot of usefull informations on Qlik Community but I'm not completely finish with my task.

So I have a table with 3 dimensions. ID, Country, Month. So in my line chart I want to count my ID for each Country and do a cumulative sum for each month. So far I have this expression:

RangeSum(above(TOTAL Count(ID),0,RowNo(TOTAL)))

But my country are not treated individualy. How can I specify that I want only a rangesum for each country?

Thanks for your help

[sunny_talwar]

Can you try this:

RangeSum(Above(Count(ID), 0, RowNo()))

[reivax31]

Hi,

With your expression (RangeSum(Above(Count(ID), 0, RowNo()))) I have a cumulative sum on Country.

With my expression (RangeSum(above(TOTAL Count(ID),0,RowNo(TOTAL)))) I have a cumulative sum on Country and Month.

... How can I have a cumulative sum only on Month? Each Country treated individualy in my chart ...

[sunny_talwar · Accepted solution]

Try this:

Aggr(RangeSum(Above(Count(ID), 0, RowNo())), Country, Month)

[reivax31]

Hi,

It worked nearly perfectly. The cumulative sum is taking each country individualy so that is great. I don't know why yet, but I will have the total amount on the first month and then it goes down until the end of the year. So I have to figure out how to make it reverse. Because usually you want to know the cumulative at the end of the year 🙂

Thanks for your great help

[sunny_talwar]

Since this is Qlik Sense, you can try this:

Aggr(RangeSum(Above(Count(ID), 0, RowNo())), Country, (Month, (NUMERIC, ASCENDING)))

[reivax31]

Function NUMERIC and ASCENDING do not exist in Qlik Sense ..

[sunny_talwar]

Did you try if it works or not? Look here

The sortable Aggr function is finally here!

[sunny_talwar]

Alternatively, you can try this:

Aggr(Count(TOTAL <Country> ID) - RangeSum(Above(Count(ID), 0, RowNo())), Country, Month)

[reivax31]

Hi,

When I put the function NUMERIC for exemple it is not reconized as a function (not in blue). So I thaught it is only Qlik View. And nothing changed because my Month is in text.

But thanks for the link I found there is also TEXT function, still it doesn't work because based on the text all the month are mixed up. But there is also LOAD_ORDER. This one worked perfectly!!

Thanks again for your great help.