Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
UserID2626
Creator III
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)
1 Solution

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

 

Hope this helps,

BR,

Vu Nguyen

View solution in original post

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

 

Hope this helps,

BR,

Vu Nguyen