topic Re: Range Sum - cummulative sums with limit date using set analysis in QlikView

Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 09:52:42 GMT

Hi Developers,

I'm trying to do cummulative sums for 12 months in bar chart within specific year regardless current selected month:

MO_ID is kind of sum.

Month = Miesiac

sty - Jan

lut - Feb...

...gru - Dec

My expression:

=sum({$<Month=>}aggr(rangesum( ABOVE( sum({$<ID_Month= {"<=$(=Max({1} ID_Miesiąc))"}, Month=, MO_ID={1}>}Amount),0,12) ),ID_Month))

This is working like this:

When i have 2016 i am limited with max ID_Month - 2 (it is the last closed month)

=sum({$<Month=, ID_Month={'<=2'}>}aggr(rangesum( ABOVE( sum({$<ID_Month= {"<=$(=Max({1} ID_Miesiąc))"}, Month=, MO_ID={1}>}Amount),0,12) ),ID_Month)

Result with this expression is:

And this result above i would to get in my chart but only in 2016. As you can see above -

I have year 2016 in lut - February so when i set the expression if(Year='2016',...) this will be working only in one row,

i want them all in 2016.

Please save me and help me,

thank you in advance !

Jacek Antek

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 09:57:20 GMT

I think that's because a grain mismatch between your aggr() dimensions and the outer chart dimensions (read Henric's blog post about aggr() pitfalls if you need to understand the grain mismatch), try

=sum({$<Month=, ID_Month={'<=2'}>}aggr(rangesum( ABOVE( sum({$<ID_Month= {"<=$(=Max({1} ID_Miesiąc))"}, Month=, MO_ID={1}>}Amount),0,12) ),Year, ID_Month)

Re: Range Sum - cummulative sums with limit date using set analysis

Gysbert_Wassenaar — Tue, 22 Mar 2016 09:59:25 GMT

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations‌

I think your expression is missing at least something that makes it ignore the selection of the year and something to aggregate per month per year instead of just over months. Can you post a small qlikview document that demonstrates the problem?

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 10:07:49 GMT

Thank You swuehl.

I have tried your solution but it didn't work.

Im wondering if i had flagYear in model it would solve my problem.

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 10:14:19 GMT

Maybe I just don't understand your data model. Why do you have year 2016 only in lut?

Would you be able to share a small sample?

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 10:23:01 GMT

MY file has 84 mb, how can i do from it small sample ?

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 10:27:30 GMT

Please don't upload a file this large, just create a small sample QVW with some (best INLINE) tables that show how relevant fields and tables are related and how your data looks like (could be mock up data).

You only need to create the relevant fields to demonstrate your setting and issue (I know it's not easy to tell what's relevant, as soon as you've mastered this, you probably also mastered to resolve the issue on your own )

Re: Range Sum - cummulative sums with limit date using set analysis

Gysbert_Wassenaar — Tue, 22 Mar 2016 10:30:40 GMT

Select some values in your document and then choose the Reduce option from the File menu and choose Keep Possible Values. Then save the document so it contains only the reduced data set.

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 10:51:57 GMT

Ok,

so my file has only 7.7 mb right now

Dictionary for you Guys:

Miesiąc - Month

Rok - Year

ID_Miesiąc = ID_Month

KwotaW - Amount.

Thank you for your support.

Jacek Antek

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 10:56:58 GMT

I can't open the QVW, seems like there is section access enabled.

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 11:00:10 GMT

Ok now should be ok.

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 11:06:32 GMT

This order of aggr() dimension values is important for the rangesum(Above()), so maybe just

=sum({$<Miesiąc=, ID_Miesiąc={'<=2'}>}aggr(rangesum( ABOVE( sum({$<ID_Miesiąc= {"<=$(=Max({1} ID_Miesiąc))"}, Miesiąc=, MO_ID={1}>}KwotaW),0,12) ),Rok,ID_Miesiąc))

edit: Well, maybe I got lost in your sample. What exactely is your requested result for the selection of 2016 and lut?

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 11:24:53 GMT

Thanks swuehl, unfortunately your solution is too simple to get worked 😉

I want to have if(Rok='2016', show aggregation only for Sty and Lut -

=sum({$<Miesiąc=, ID_Miesiąc={'<=2'}>}aggr(rangesum( ABOVE( sum({$<ID_Miesiąc= {"<=$(=Max({1} ID_Miesiąc))"}, Miesiąc=, MO_ID={1}>}KwotaW),0,12) ),Rok,ID_Miesiąc))

, and if you have Rok = '2015' show all month with cummulative sums:

=sum({$<Miesiąc=>}aggr(rangesum( ABOVE( sum({$<ID_Miesiąc= {"<=$(=Max({1} ID_Miesiąc))"}, Miesiąc=, MO_ID={1}>}KwotaW),0,12) ),Rok,ID_Miesiąc))

The problem is when i have Rok = '2015' it is connected with month which i selected.

So my "if" will be working only there where i have Rok. In 2016 i want to have Rok not only where i selected. I want to have this as well in Jan and in Feb.

Re: Range Sum - cummulative sums with limit date using set analysis

swuehl — Tue, 22 Mar 2016 11:33:32 GMT

This should show the year in all dimension lines

=Only({<Miesiąc=>}Rok)

But maybe you just need to filter in your set expression instead of using an if() statement, like

=sum({$<Miesiąc=, ID_Miesiąc={'<=2'}, Rok *= {2016}>}aggr(rangesum( ABOVE( sum({$<ID_Miesiąc= {"<=$(=Max({1} ID_Miesiąc))"}, Miesiąc=, MO_ID={1}>}KwotaW),0,12) ),Rok,ID_Miesiąc))

or use conditional expressions?

Re: Range Sum - cummulative sums with limit date using set analysis

Anonymous — Tue, 22 Mar 2016 12:53:35 GMT

Yes, it is working - =Only({<Miesiąc=>}Rok):

but when i set this in expression in my model there was still problem with this.

I upgraded my expression and now it is working and looking like that:

=if(Only({<Miesiąc=, ID_Miesiąc=>}Rok)=2016,

sum({$<Miesiąc=, Kwartał=, ID_Miesiąc= {"<=2"}, Rok = {2016}>}aggr(rangesum( ABOVE( sum({$<ID_Miesiąc= {"<=$(=Max({1} ID_Miesiąc))"}, Kwartał=,Miesiąc=, MO_ID={1}>}KwotaW),0,12) ),ID_Miesiąc))

So the key was to aggregate Rok except of current selections.

Thank You swuehl for your help !

Jacek Antek