Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
blpetosa
Contributor III
Contributor III

Running Total in Table

Hello Everyone,

I am having issues creating a field that is a running total of another. I am using "group by" in the load statement to get engagement by date. I either get a very large number, or the same number as the engagement field. Here is simple example:

Date          Engagement          Total Engagement

1/1/17               4                                   4

1/2/17               5                                   9

1/3/17               16                                 25

etc......

After I get the running total, I need to find the date where the total hits the engagement goal, which will be the 4th field (not shown).

I appreciate any help I can get with this.

Thanks,

Ben

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

OR using front end you can use Above with RangeSum like:

Straight table:

Dim: DateField, Engagement

Expr: = Aggr(RangeSum(Above(TOTAL Sum(Engagement), 0,RowNo())), DateField)

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Picked from one of Sunny's recent script level If statement you can use like:

LOAD *,

     IF(Engagement = Previous(Engagement), Peek('Total'), RangeSum(Peek('Total'), Engagement)) AS Total;

LOAD * INLINE [

DateField ,         Engagement  

1/1/17,               4                                 

1/2/17 ,              5                                 

1/3/17  ,             16

];   

Capture.PNG

vishsaggi
Champion III
Champion III

OR using front end you can use Above with RangeSum like:

Straight table:

Dim: DateField, Engagement

Expr: = Aggr(RangeSum(Above(TOTAL Sum(Engagement), 0,RowNo())), DateField)

blpetosa
Contributor III
Contributor III
Author

Thanks Vishwarath! This worked on both ends.

vishsaggi
Champion III
Champion III

Glad. Can you close the thread marking correct/helpful responses accordingly.

ninja
Contributor
Contributor

Works for me ,  thanks for this !