Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yossi_bire
New Contributor II

Cumulative sum between dates

Hi

I'm trying to create a cumulative aggregation between dates.

I have an order table with ORDER_NUMBER, ORDERED_DATE, INVOICE_DATE, INCOME

The end result needs to be a Bar Chart that presents the income of the orders that the Quarter of the invoice greater from the ordered date

see the example below

  1. The first order needs to be calculated for periods 2018-Q1 until 2018-Q3
  2. The second order does not need to be calculated because of the ordered date and the invoice date in the same quarter
  3. The Last order needs to be calculated for period 2018-Q2 only
ORDER_NUMBERORDERED_DATEINVOICE_DATEINCOME
1111102/01/201802/10/2018100
2222202/05/201820/05/2018300
3333302/06/201803/08/2018400

 

YearQuarterINCOME
2018-Q1100
2018-Q2500
2018-Q3100
2018-Q40

 

Labels (3)
3 Replies
zzyjordan
Contributor II

Re: Cumulative sum between dates

Hi, 

Please find my solution by join your data to a calendar table as shown below

Untitled.jpg

 

and then create a measure expression as below

Untitled1.jpg

Hope this helps

 

ZZ

yossi_bire
New Contributor II

Re: Cumulative sum between dates

Hi,

Thanks, but the original table contains more than 80 columns, and I'm trying to avoid using IF statements.

Is there a way to do that with Aggr and RangeSum with Set Analysis?

zzyjordan
Contributor II

Re: Cumulative sum between dates

as the condition is dynamic, even with set analysis, you need some sort of if condition in the expression. And I am not quite understand why the number of columns will impact the if statements, as this is just compare two columns in your data set.