Max date in sum aggr (different max-Date per dimension possible)

[richters]

Hi all,

I am currently facing a problem. My formula is as followed:

sum(
     aggr(
          Sum ({<Entlassungsjahr={'$(vJahr_m1KH)'},NumberofMonth={'<=$(vMaxEntlMon)'}>} Value)
     ,dimension1,dimension2
     )
)

The problem is that the variable '$(vMaxEntlMon)' should consist the max month in the max({ <Entlassungsjahr={'$(vJahrAktKH)'} EntlassungsmonatZahl) per dimension1,dimension2 which are the aggr-dimensions. The problem is that as it is a variable the number is always the same, namely the max-value over all.

How do I get the max({ <Entlassungsjahr={'$(vJahrAktKH)'} EntlassungsmonatZahl) in the set analysis for each aggr-dimension?

Thanks in advance!

[sunny_talwar]

Set analysis will be evaluated once per chart, so what you want is not possible using set analysis. But you might be able to use Aggr() function to do this. Would you be able to share a sample with the desired output?

[richters]

Thanks for the information! See file attached ("easy" example).

What is needed:

The sum of quantity of YearM1 until MaxMonth in MaxYear for each product - subproduct combination.

Which means (see also table MaxMonth):

A-A1 from 2015-01-01 to 2015-07 = 19

A-A2 from 2015-01-01 to 2015-04 = 19

B-B1 from 2015-01-01 to 2015-08 = 22

B-B2 from 2015-01-01 to 2015-05 = 30

summed up by main_product and in total.

I want to calculate the expansion for the total year on the dimension of sub-product and main-product (because I get the numbers not very regularly). But I don't want to show it this detailed (just main_product or just one total - row).

Thanks in advance.

[sunny_talwar]

Try this:

Sum({<Year_Sold={'$(vMaxYearM1)'}>} Aggr(If(MonthNum_Sold <= Max(TOTAL <main_product, sub_product> {<Year_Sold={$(=Max(Year_Sold))}>} MonthNum_Sold), Sum({<Year_Sold={'$(vMaxYearM1)'}>}Quantity))

        , sub_product, main_product, MonthNum_Sold

        ) 

    )