Running Total in Table

[blpetosa]

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

[vishsaggi]

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

];   

[vishsaggi · Accepted solution]

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]

Thanks Vishwarath! This worked on both ends.

[vishsaggi]

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

[ninja]

Works for me ,  thanks for this !