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

[Anonymous]

Ok now should be ok.

[swuehl]

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?

[Anonymous]

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.

[swuehl · Accepted solution]

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?

[Anonymous]

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