Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Carry Last Value/Running Total Problem

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

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

Not applicable

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

Community Browser