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

    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:

      DayKeyRuleSales
      1AX100
      1AY150
      1BX50
      2AX200
      2AY300
      2BX10
      3AX700
      3AY100
      4AY15

       

      Table B:

      DayTotal
      1300 (100+150+50)
      2510 (200+300+10)
      3810 (700+100+10)
      4725 (15+700+10)

       

       

       

      Thanks