Range Sum - cummulative sums with limit date using set analysis

[Anonymous]

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

[swuehl]

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)

[Gysbert_Wassenaar]

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?

[Anonymous]

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.

[swuehl]

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?

[Anonymous]

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

[swuehl]

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 )

[Gysbert_Wassenaar]

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.

[Anonymous]

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

[swuehl]

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