topic Re: Running total (again)... in App Development

Running total (again)...

curiousfellow — Thu, 21 Feb 2019 09:13:16 GMT

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

Re: Running total (again)...

pradosh_thakur — Thu, 21 Feb 2019 09:20:47 GMT

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

Re: Running total (again)...

curiousfellow — Thu, 21 Feb 2019 09:36:26 GMT

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

Re: Running total (again)...

pradosh_thakur — Thu, 21 Feb 2019 09:53:07 GMT

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))

Re: Running total (again)...

curiousfellow — Thu, 21 Feb 2019 10:02:32 GMT

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

Re: Running total (again)...

pradosh_thakur — Thu, 21 Feb 2019 10:23:59 GMT

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

Re: Running total (again)...

curiousfellow — Thu, 21 Feb 2019 11:18:45 GMT

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.

Re: Running total (again)...

pradosh_thakur — Thu, 21 Feb 2019 11:25:19 GMT

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

Re: Running total (again)...

curiousfellow — Thu, 21 Feb 2019 12:17:56 GMT

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.

Re: Running total (again)...

pradosh_thakur — Thu, 21 Feb 2019 12:33:27 GMT

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.

Re: Running total (again)...

curiousfellow — Thu, 21 Feb 2019 13:46:53 GMT

load * inline [
%key_kpl	,yearmonthnumber	,	WTF_Totaal
2018-5300,24217,25
2018-5300,24218,25
2018-5300,24219,25
2018-5300,24220,25
2018-5300,24221,25
2018-5300,24222,25
2018-5300,24223,25
2018-5300,24224,25
2018-5300,24225,25
2018-5300,24226,25
2018-5300,24227,25
2018-5300,24228,25
2018-5300,24229,25
2018-5300,24230,25
2018-5300,24231,25
2018-5300,24232,25
2018-5300,24233,25
2018-5301,24217,25
2018-5301,24218,25
2018-5301,24219,25
2018-5301,24220,25
2018-5301,24221,25
2018-5301,24222,25
2018-5301,24223,25
2018-5301,24224,25
2018-5301,24225,25
2018-5301,24226,25
2018-5301,24227,25
2018-5301,24228,25
2018-5301,24229,25
2018-5301,24230,25
2018-5301,24231,25
2018-5301,24232,25
2018-5301,24233,25


];

And the result is below this . After yearmonthnum 24228 the running total should remain 300. (12*25)

%key_kpl	yearmonthnumber	=if(above([%key_kpl])=[%key_kpl] ,rangesum(above(column(1)),WTF_Totaal),sum(WTF_Totaal))
2018-5300	24217	25
2018-5300	24218	50
2018-5300	24219	75
2018-5300	24220	100
2018-5300	24221	125
2018-5300	24222	150
2018-5300	24223	175
2018-5300	24224	200
2018-5300	24225	225
2018-5300	24226	250
2018-5300	24227	275
2018-5300	24228	300
2018-5300	24229	325
2018-5300	24230	350
2018-5300	24231	375
2018-5300	24232	400
2018-5300	24233	425
2018-5301	24217	25
2018-5301	24218	50
2018-5301	24219	75
2018-5301	24220	100
2018-5301	24221	125
2018-5301	24222	150
2018-5301	24223	175
2018-5301	24224	200
2018-5301	24225	225
2018-5301	24226	250
2018-5301	24227	275
2018-5301	24228	300
2018-5301	24229	325
2018-5301	24230	350
2018-5301	24231	375
2018-5301	24232	400
2018-5301	24233	425