Running total (again)...

[curiousfellow]

In this helpfull forum I  found an expression that seems to be the solution for what I am looking for.

This is the expression :

=sum(aggr(rangesum(above(total sum({<yearmonthnumber= >}WTF_Totaal),0,12)),yearmonthnumber))

I get strange results however.The cumulative sum has the wrong order.  In my app I only have one table. For every %key_kpl /yearmonthnumber there is only one value. No matter what %key_kpl I select, the cumulative sum gets the wrong order at yearmonthnumber 24223.

Yearmonthnumber is calculated in my load script  2018*12+num(right(period,2))  where period is  '2018-05' for example.

When I reduce the number of %key_kpl to one or two  then the cumulative values are correct. So I assume it has something to do with the sorting in the load script, I tried several sorting but cumulatives remain wrong.

I spent several hours figuring out what the reason of this error can be, but now I am out of ideas.

I hope someone can give me a hint on how to solve this. We are version 12.10.20600.0. of Qlikview

%key_kpl	yearmonthnumber	**bleep**  WTF_Totaal	=WTF_Totaal'
2018-5300	24217	2.50	2.50
2018-5300	24218	5.00	2.50
2018-5300	24219	7.50	2.50
2018-5300	24220	10.00	2.50
2018-5300	24221	12.50	2.50
2018-5300	24222	15.00	2.50
2018-5300	24223	27.50	2.50
2018-5300	24224	30.00	2.50
2018-5300	24225	17.50	2.50
2018-5300	24226	20.00	2.50
2018-5300	24227	22.50	2.50
2018-5300	24228	25.00	2.50

[pradosh_thakur]

=rangesum(above(sum(WTF_Totaal),0,rowno()))

[curiousfellow]

It is my intention to cumulate 12 rows, so rowno() isn't logic I think ?

[pradosh_thakur]

load * inline [
%key_kpl ,yearmonthnumber , WTF_Totaal
2018-5300, 24217, 2.50
2018-5300, 24218, 2.50
2018-5300, 24219, 2.50
2018-5300, 24220, 2.50
2018-5300, 24221, 2.50
2018-5300, 24222, 2.50
2018-5300, 24223, 2.50
2018-5300, 24224, 2.50
2018-5300, 24225, 2.50
2018-5300, 24226, 2.50
2018-5300, 24227, 2.50
2018-5300, 24228, 2.50
];

DIMENSION

%key_kpl

yearmonthnumber

expression

if(above([%key_kpl])=[%key_kpl] ,rangesum(above(column(1)),WTF_Totaal),sum(WTF_Totaal))

[curiousfellow]

AS said, I get the correct results when there is only one %key_kpl. HOwever it gets wrong when there are several. I still think it has something to do with sorting in the load script, but don't know what

[pradosh_thakur]

I dont know how youa re sorting it. Sort it by %key first the by yearmonth. the same expression is working for me. Below is the sample data. remember to sort it by %keykpl first then by yearmonthnumber. Else post you app. I am pretty sure this will work

load * inline [
%key_kpl	,yearmonthnumber	,	WTF_Totaal
2018-5300,	24217,		2.50
2018-5300,	24218,		2.50
2018-5300,	24219,		2.50
2018-5300,	24220,		2.50
2018-5300,	24221,		2.50
2018-5300,	24222,		2.50
2018-5300,	24223,		2.50
2018-5300,	24224,		2.50
2018-5300,	24225,		2.50
2018-5300,	24226,	2.50
2018-5300,	24227,		2.50
2018-5300,	24228,		2.50
2018-5301,	24217,		3.50
2018-5301,	24218,		3.50
2018-5301,	24219,		3.50
2018-5301,	24220,		3.50
2018-5301,	24221,		3.50
2018-5301,	24222,		3.50
2018-5301,	24223,		3.50
2018-5301,	24224,		3.50
2018-5301,	24225,		3.50
2018-5301,	24226,	3.50
2018-5301,	24227,		3.50
2018-5301,	24228,		3.50

];

use the exact expression i am using

[curiousfellow]

Seems to run well at first sight. But how do I achieve now that the running total will be limited to 12 months. ?
I appreciate your help. Don't see the logic why the running total does not run well with my first expression.

[pradosh_thakur]

i am sorry i didn't get you .

1: did my expression worked for you ?

2: Do you want me to update your expression using rangesum and not use the expression i have given now?

Let me know so according to that i can help you out.

-Pradosh

[curiousfellow]

In the original table I have more than 12 yearmonthnumbers for every %key_kpl. For every month the number increases with one.
The goal is to sum current yearmonthnum and the preceeding 11 months. When you would help to achieve this I would appreciate that very much.

[pradosh_thakur]

I  beleive this is what we are exactly doing, can you please come up with sample data for two %key_lpla nd the sample output. My be i am not able to get the requirment clear.