# New to Qlik Sense

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

Announcements
cancel
Showing results for
Did you mean:
Creator III

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

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 🙂

Labels (4)

• ### Set Analysis

1 Solution

Accepted Solutions
Creator III

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:
"Date",
Value,
\$(vRollingSumExp) as RollingSum
FROM [lib://Data/Data.xlsx] (ooxml, embedded labels, table is Sheet3);``````

Hope this helps,

BR,

Vu Nguyen

Creator III

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: