12 Replies Latest reply: Aug 18, 2017 7:15 AM by Sunny Talwar

# How to calculate cumulative data in Qlik Sense?

Dear Qlik Sense Experts

I am new to Qlik Sense and I am a front end user

Now my raw data is as below. First line shows how many orders shipped in specific days

Take [E2] as an example, it means there are 4 orders take 6 days to ship from plant

Now I want to add 2nd line to calculate the cumulative orders which shipped within specific days

Take [F3] as an example, there are 26 orders ships within 7 days

These 26 orders include:

1 order takes 5 days to ship

4 orders take 6 days to ship

21 orders take 7 days to ship

So 26 [F3] = [B2]+[C2]+[D2]+[E2]+[F2]

May I  know what expression I should use in Qlik Sense to make this request happen?

You could refer to raw data in attachment

Ivan

• ###### Re: How to calculate cumulative data in Qlik Sense?

Try this (assuming you are doing this in a pivot table with your Cnty dimension pivoted)

RangeSum(Before(Count([Orders Shipped]), 0, ColumnNo()))

• ###### Re: How to calculate cumulative data in Qlik Sense?

Dear Sunny

Thank you so much for your prompt, it works!

We have an additional request to calculate the cumulative percentage

I try to use below expression to calculate such percentage but it fails. I assume the data in RangeSum expression will change according  to below formula

RangeSum(Before(Count([Order Line]), 0, ColumnNo())) / Count ([Order Line])

May I know if there is any way to achieve this request?

Correct Cum Data

Incorrect Cum Rate

Ivan

• ###### Re: How to calculate cumulative data in Qlik Sense?

May be this

RangeSum(Before(Count([Order Line]), 0, ColumnNo())) / Count(TOTAL [Order Line])

• ###### Re: How to calculate cumulative data in Qlik Sense?

Dear Sunny

Thank you so much for the reply. It works again!

I am sorry that I have one last question, when I turn the pivot table into line chart, the measurement we set up in master item somehow turn to 0. It should shows the same percentage as the pivot tablet

Does line chart can't identify the data in pivot tablet as data in it are cumulative ones?

Ivan

• ###### Re: How to calculate cumulative data in Qlik Sense?

Try this for a line chart

RangeSum(Above(Count([Order Line]), 0, RowNo())) / Count(TOTAL [Order Line])

• ###### Re: How to calculate cumulative data in Qlik Sense?

Dear Sunny

Sorry for my late response. I try your advise but it doesn't work in Qlik

The result in line chart should be as below but it turn out to be what shows in 2nd screenshot

Ivan

• ###### Re: How to calculate cumulative data in Qlik Sense?

You have two dimensions? Try this

1) Dimension1

2) Dimension2

Aggr(RangeSum(Above(Count([Order Line]), 0, RowNo())) / Count(TOTAL <Dimension2> [Order Line]), Dimension2, Dimension1)

Replace Dimension1 with your chart's dimension1 and replace Dimension2 with your chart's dimension2

• ###### Re: How to calculate cumulative data in Qlik Sense?

Sunny

Yes there are 2 dimension in this app as below

I try your expression but the line chart doesn't have the correct result. The percentage in line chart should match the above pivot table

I have put the apps and raw data in the attachment, would you mind take a look?

Ivan

• ###### Re: How to calculate cumulative data in Qlik Sense?

This?

Aggr(RangeSum(Above(Count([Order Line]), 0, RowNo())) / Count(TOTAL <[Sold to Country]> [Order Line]),[Sold to Country], ([OTS Days(CD)], (NUMERIC)))

• ###### Re: How to calculate cumulative data in Qlik Sense?

Dear Sunny

I am sorry for the late response

It works! Thank you so much for the patience and guidance.

One small question here, what is the function of (NUMERIC) in this expression?