Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
yossi_bire
Contributor II
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 (1)
3 Replies
zzyjordan
Creator II
Creator II

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
Contributor II
Contributor II
Author

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
Creator II
Creator II

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.