Sum of Current Date+(Date-30) Qlik sense

[UserID2626]

Hi,

I need to show below table where dimension is Date & Measure is Value.

I need to Add another column Sum of 30 Days which is addition of 30 Days Value with Current Date.

Date	Value	Sum of 30 days	Logic
21-Jan-19	23
22-Jan-19	45
23-Jan-19	7
24-Jan-19	12
25-Jan-19	45
26-Jan-19	89
27-Jan-19	23
28-Jan-19	65
29-Jan-19	24
30-Jan-19	45
31-Jan-19	5
1-Feb-19	7
2-Feb-19	85
3-Feb-19	11
4-Feb-19	36
5-Feb-19	5
6-Feb-19	0
7-Feb-19	4
8-Feb-19	8
9-Feb-19	2
10-Feb-19	56
11-Feb-19	7
12-Feb-19	5
13-Feb-19	12
14-Feb-19	1
15-Feb-19	2
16-Feb-19	6
17-Feb-19	55
18-Feb-19	52
19-Feb-19	1
20-Feb-19	5
21-Feb-19	2	743	Sum of 21-Jan-19 to 20-Feb-2019
22-Feb-19	6	722	Sum of 22-Jan-19 to 21-Feb-2019

I tried with Front end Part because of date field its not working, Help me with Script Part.

Thanks 🙂

[vunguyenq89 · Accepted solution]

Hi,

To calculate a rolling sum of previous n rows in reload script, you can use the following expression:

RangeSum(Peek(Value,-1),Peek(Value,-2),...,Peek(Value,-n))

It could be a little bit repetitive to write the expression when n is large (n=30 in this case). However, you can automate the generation of this expression with a for loop.

Sample script as follows. Modify the Call RollingSum(30,'Value') statement to  the number of days and value field name as you need.

Sub RollingSum(days,fieldName)
	SET vRollingSumExp = "If(RecNo() > $(days),RangeSum(";
    For i = 1 to days
        vPeek = 'Peek($(fieldName),-$(i)),';
        vRollingSumExp = vRollingSumExp & vPeek;
    Next i;
    vRollingSumExp = Left(vRollingSumExp,Len(vRollingSumExp)-1) &  '))';
End Sub;

Call RollingSum(30,'Value');

Data:
LOAD
    "Date",
    Value,
    $(vRollingSumExp) as RollingSum
FROM [lib://Data/Data.xlsx] (ooxml, embedded labels, table is Sheet3);

Hope this helps,

BR,

Vu Nguyen