Can I add set analysis to group by field inside Aggr()?

[fionna51]

Hi,

I have a very regular score calculation which the logic like: Score= sum(Numerator * Weight) / sum(Weight) for all measures. Those numerator and weight are for each measure. So, I have to group by MeasureID. In order to compare, I have to show current year score and prior year one. Now I met a problem, measures may change from year to year.  In my example, there are 3 measures in 2014 but only two in 2014. So, when calculated 2015 and 2014 score at the same time, 2014 score only takes 2 measures same as 2015. 

When 2014 is selected, we can see the score is 8.7, but when 2015 is selected, the prior score which is 2014 shows 7.8. This is because only two 2015 measures are counted for 2014, although it took correct 2014 data by set analysis.

So, the aggr function for prior year score showing below, how can I take all possible measures for 2014?

round(
sum(Aggr(sum({<Year={$(=only(Year)-1)}>} Numerator)*only({<Year={$(=only(Year)-1)}>} Weight), MeasureID))
/
Sum(Aggr(Max({<Year={$(=only(Year)-1)}>}Weight) , MeasureID))
,0.1)

Thanks in advance.

[MK_QSL]

may be

round(

sum(Aggr(sum({<Year = {'$(=Max(Year))'}>}Numerator)*only({<Year = {'$(=Max(Year))'}>}Weight), MeasureID,Year))

/

Sum(Aggr(Max({<Year = {'$(=Max(Year))'}>}Weight) , MeasureID,Year))

,0.1)

and

round(

sum(Aggr(sum({<Year = {'$(=Max(Year)-1)'}>}Numerator)*only({<Year = {'$(=Max(Year)-1)'}>}Weight), MeasureID,Year))

/

Sum(Aggr(Max({<Year = {'$(=Max(Year)-1)'}>}Weight) , MeasureID,Year))

,0.1)

[rubenmarin · Accepted solution]

Hi Fion, you can try the last year expression as:

round(

sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(sum({<Year={$(=only(Year)-1)}>} Numerator)*only({<Year={$(=only(Year)-1)}>} Weight), MeasureID))

/

Sum(TOTAL {<Year={$(=only(Year)-1)}>}Aggr(Max({<Year={$(=only(Year)-1)}>}Weight) , MeasureID))

,0.1)