3 Replies Latest reply: Jan 5, 2015 11:28 AM by Brendan Sheehy

# Carry Last Value/Running Total Problem

I am trying to come up with an expression that will ultimately give me the Total column in Table B below.  I have a large data set that contains the fields Day, Key, Rule, and Sales.

The Total column in Table B is the running total with respect to each Key/Rule.  For example, on Day 1 there is 1 Sales value for every combination of Key/Rule.  So for Day 1 the Total is simply 100+150+50 = 300.  However, on Day 3 there is only a Sales value for 2 of the Key and Rule combinations.  The Total for Day 3 is 700+100+10 = 810.  The +10 comes from the latest Sales quantity for Key B, Rule X on Day 2.  And for Day 4 the Total is 15+700+10: The +700 and +10 are carried from the latest dates for Key A/Rule X and Key B/Rule X respectively.  Don't get confused by the Key/Rule relationship... if it's easier you can concatenate these 2 columns to create a unique identifier (i.e. AX, AY, and BX).

Any help would be greatly appreciated.

Table A:

 Day Key Rule Sales 1 A X 100 1 A Y 150 1 B X 50 2 A X 200 2 A Y 300 2 B X 10 3 A X 700 3 A Y 100 4 A Y 15

Table B:

 Day Total 1 300 (100+150+50) 2 510 (200+300+10) 3 810 (700+100+10) 4 725 (15+700+10)

Thanks

• ###### Re: Carry Last Value/Running Total Problem

See attached qvw.

• ###### Re: Carry Last Value/Running Total Problem

Gysbert,

This solution works.  However, is there any way this can be done through a single expression in a chart and not in the script?  The reason I ask is because the table that holds this data using your solution is now hundreds of millions of rows.  We have thousands of keys and dozens of rules so you can imagine there are many combinations.

Thanks

• ###### Re: Carry Last Value/Running Total Problem

Gysbert,

After looking into this further I think this will definitely need to be in an expression.  Your solution works but if I, for example, want to only look at Key B/Rule X it will show Days 1-4 even though this particular Key/Rule should only show days 1-2.  Let me know if you need any further clarification.

Thanks