Announcements
cancel
Showing results for
Did you mean:
Partner - Creator III

## Rangesum cumulative with 2 dimensions

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?

1 Solution

Accepted Solutions
MVP

Try this:

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

10 Replies
MVP

Can you try this:

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

Partner - Creator III
Author

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 ...

MVP

Try this:

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

Partner - Creator III
Author

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 🙂

MVP

Since this is Qlik Sense, you can try this:

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

Partner - Creator III
Author

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

MVP

Did you try if it works or not? Look here

The sortable Aggr function is finally here!

MVP

Alternatively, you can try this:

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

Partner - Creator III
Author

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.