topic Re: Running Total in Table in QlikView

Running Total in Table

blpetosa — Thu, 27 Jul 2017 12:02:12 GMT

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

Re: Running Total in Table

vishsaggi — Thu, 27 Jul 2017 16:13:17 GMT

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

];

Re: Running Total in Table

vishsaggi — Thu, 27 Jul 2017 16:27:17 GMT

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)

Re: Running Total in Table

blpetosa — Thu, 10 Aug 2017 18:01:31 GMT

Thanks Vishwarath! This worked on both ends.

Re: Running Total in Table

vishsaggi — Thu, 10 Aug 2017 18:22:55 GMT

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

Re: Running Total in Table

ninja — Mon, 18 Nov 2019 15:52:33 GMT

Works for me , thanks for this !