Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Running total (again)...

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_kplyearmonthnumber**bleep**  WTF_Totaal=WTF_Totaal'
2018-5300242172.502.50
2018-5300242185.002.50
2018-5300242197.502.50
2018-53002422010.002.50
2018-53002422112.502.50
2018-53002422215.002.50
2018-53002422327.502.50
2018-53002422430.002.50
2018-53002422517.502.50
2018-53002422620.002.50
2018-53002422722.502.50
2018-53002422825.002.50

 

 

10 Replies
Highlighted
Master II
Master II

=rangesum(above(sum(WTF_Totaal),0,rowno()))
Learning never stops.
Highlighted
Creator II
Creator II

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

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))
Learning never stops.
Highlighted
Creator II
Creator II

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
Highlighted
Master II
Master II

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

Learning never stops.
Highlighted
Creator II
Creator II

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.
Highlighted
Master II
Master II

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

Learning never stops.
Highlighted
Creator II
Creator II

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.
Highlighted
Master II
Master II

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.

Learning never stops.