Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

3 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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