Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

See attached qvw.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

Highlighted
Not applicable

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