Running Total with Conditions

[rmooney-jvchc]

I'm needing to create an Expression that is the running total based on two criteria.  First, it needs to be grouped by an ID.  Second, it needs to add only values from a third column from that month and previous months.  Below is a simple data example.  I have the ID, Month/Year, and Value columns and need to figure out the Running Total column.

ID          Month               Value          Running Total

1            201702               3                         3

1            201704               -1                        2

1            201710               -1                        1

2            201611               2                          2

2            201612               2                          4

2            201711               -1                         3

3            201508               5                          5

3            201801               -2                         3

So the Running Total is taking the Sum from the previous Month and adding to it the value of that Month.  Notice that the months aren't sequential.  Also, I can't use any static functions that would break if the chart was sorted or filtered differently.

Any help would be greatly appreciated!!

[juraj_misina]

Hi Rob,

there are basically two options. You either use Above() function, which is dependand on the chart sort order, so does not really meet your requirement, or you precalculate this field in script (which can of course cause it's own challenges). I guess I'd go with the second option, given it is doable with the data and other requirements you have.

Juraj

[rmooney-jvchc]

I'm not sure how to do it in the script.  I was trying to accomplish this through my SQL query but was unable to so I thought I would see if QlikView could do it easier.

[Anil_Babu_Samineni]

You want this in script / UI?

Perhaps this?

Above(TOTAL [Running Total],0,1) // where [Running Total] is the label for measure which includes {Sum(value)}

[rmooney-jvchc]

Won't that break when the sort order is changed or a filter applied?

[sunny_talwar]

If you have QV12 or above, the sort order can be handled using Aggr() function (The sortable Aggr function is finally here!) and this can still work with selection using set analysis...