Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

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?

Thanks for your help

Tags (2)
1 Solution

Accepted Solutions

Re: Rangesum cumulative with 2 dimensions

Try this:

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

View solution in original post

10 Replies

Re: Rangesum cumulative with 2 dimensions

Can you try this:

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

Partner
Partner

Re: Rangesum cumulative with 2 dimensions

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

Re: Rangesum cumulative with 2 dimensions

Try this:

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

View solution in original post

Partner
Partner

Re: Rangesum cumulative with 2 dimensions

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

Re: Rangesum cumulative with 2 dimensions

Since this is Qlik Sense, you can try this:

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

Partner
Partner

Re: Rangesum cumulative with 2 dimensions

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

Re: Rangesum cumulative with 2 dimensions

Did you try if it works or not? Look here

The sortable Aggr function is finally here!

Re: Rangesum cumulative with 2 dimensions

Alternatively, you can try this:

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

Partner
Partner

Re: Rangesum cumulative with 2 dimensions

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.