Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ivanyang25
New Contributor III

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?


捕获.PNG


You could refer to raw data in attachment

Thank you for your advice in advance

Ivan

Tags (1)
1 Solution

Accepted Solutions

Re: How to calculate cumulative data in Qlik Sense?

This?

Capture.PNG

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

12 Replies

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()))

ivanyang25
New Contributor III

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?

01.PNG

Correct Cum Data

02.PNG

Incorrect Cum Rate

03.PNG

Thanks again for your valuable reply

Ivan    

Re: How to calculate cumulative data in Qlik Sense?

May be this

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

ivanyang25
New Contributor III

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?

捕获.PNG

Ivan

soniya_dsa
New Contributor

Re: How to calculate cumulative data in Qlik Sense?

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

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])

ivanyang25
New Contributor III

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

Could you help advise?

捕获.PNG

2.PNG

Thank you for your advise in advance

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

ivanyang25
New Contributor III

Re: How to calculate cumulative data in Qlik Sense?

Sunny

Thank you for your reply again.

Yes there are 2 dimension in this app as below

2.PNG

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?

1.PNG

Thank you for your advise in advance

Ivan

Community Browser