Accumulation of value in last x days for each day

[JoshPark]

Hello everyone, 

We have purchased licenses for Qlik Sense Enterprise SaaS. 
I'm not really the technical guy but I'm trying to find solution to calculate the accumulation of value (quantity, sales,...) in last x days for each day. 

Here is the sample data: 

I want to add a measure to show: 
- Qty of last 7 days from today is: 0+0+10+6+3+10+2 = 31 
- Qty of last 7 days from yesterday is: 0+10+6+3+10+2+0 = 31
- Qty of last 7 days from 14 Jan is: 10+6+3+10+2+0+5 = 36 
- Qty of las 7 days from 13 Jan is: 6+3+10+2+0+5+14 = 40
... (get all results for each day)

Does anyone have a solution?

Thank you!

[SerhanKaraer]

Hello Linh,

Use this as a measure.

Aggr(RangeSum(Above(Sum("Qty (A)"),0,7)),(Date,(NUMERIC,ASCENDING)))

[ogster1974]

Try

RangeSum(Above([Qty],0,7))

[JoshPark]

Hi @SerhanKaraer ,

Thank you so much for your suggestion! 

Could you share the document of the expressions which you use so I can understand the logic of the formula?

Because I think your solution seems the right method to follow; however, the expected result maybe one line different. 

That's why I want to study the expression to adjust it. 

Thank you so much!

[khaoulaaa]

Hello,

And if you try the accumulation property 

[JoshPark]

Hi @khaoulaaa 

Thank you for your comment!

I also read the documentation on Accumulation function under Modifier. 

However my measure just show these 2 options:

I have already created a case prior to this topic to ask where I have done wrong and still waiting for the support. 

In case you have any idea to fix it, please kindly share it.

Thank you so much!

[khaoulaaa]

Hello,

Yes in the cloud environment I have the same issue, if I find a solution I'll share it with you !

[SerhanKaraer · Accepted solution]

SET DateFormat='M/D/YY';

LOAD * INLINE [
Date, Qty
1/15/23, 0
1/14/23, 0
1/13/23, 10
1/12/23, 6
1/11/23, 3
1/10/23, 10
1/9/23, 2
1/8/23, 0
1/7/23, 5
1/6/23, 14
1/5/23, 26
1/4/23, 4
];

Given a sort, Above function creates a range of rows specified as parameters, in this case 0 offset and 7 rows below.

Aggr(RangeSum(Above(Sum("Qty (A)"),0,7)),(Date,(NUMERIC,ASCENDING)))

You can get the same result by changing sort and direction for rows.

Aggr(RangeSum(Below(Sum("Qty (A)"),0,7)),(Date,(NUMERIC,DESCENDING)))

 You have to define it in a aggr function as when user changes the sort by pressing the title in the chart, your expression may execute wrong.