Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarjagga
Creator
Creator

Set Analysis based on date range

Hi All,

I have a requirement where i need to calculate the sum amount in an interval and the interval will be different for each id.

For example - i have below 2 tables in my data model-

IDStart date
11/1/2019
21/10/2019
34/5/2019

 

IDAmountDate
1102/2/2019
110010/14/2019
21103/2/2019
253/3/2020
2154/3/2019
3204/10/2019
3304/13/2019
3404/20/2019

   

So i need to find out the sum for each id where Date >= StartDate and Date <= AddMonths(Addmonths(StartDate),6).

Since we have different start date for each id so not able to expand the StartDate directly in where condition.

Expected output is

IDAmount
110(10+0)
2125(110+15)
390(20+30+40)

 

For now I have used below expression with if condition -

Aggr(Sum(if(Date>= Min(StartDate) and Date <= AddMonths(Min(Date),6),Amount),ID)

Can you please suggest me some better solution. I need to do using set expression.

Labels (1)
0 Replies