topic Re: Carry Last Value/Running Total Problem in QlikView

Carry Last Value/Running Total Problem

Fri, 02 Jan 2015 19:44:38 GMT

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

Gysbert_Wassenaar — Sat, 03 Jan 2015 09:38:27 GMT

See attached qvw.

Re: Carry Last Value/Running Total Problem

Mon, 05 Jan 2015 13:41:46 GMT

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

Mon, 05 Jan 2015 16:28:22 GMT

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