Sum based on aggregation of multiple dimension.

[#1]

Hello QV community,

I am trying to calculate the Total Lives for recent months of the associated/selected Plans.

As can be seen in the image the Total Lives should be 13+16=29 for selected plans A and C for Month=6 and Month=7 respectively.

I have tried some modification of the below expression, but couldn't figure out the correct result.

Expression-> =sum(aggr(max(Month),Plan),Lives)

=aggr(sum(Lives),max(Month),Plan)

Would really be thankful for the help.

-Abhishek Anand

[swuehl · Accepted solution]

Maybe something like

=Sum( Aggr( FirstSortedValue( Lives, -Month), Plan))

[sunny_talwar]

May be this:

Sum(Aggr(FirstSortedValue(Lives, -Month), Plan))

[#4]

Thanks for the solution.

I am trying the below expression to achieve the same functionality. It is working fine when I select any plan but when  no plan is selected from the plan list box I am getting Lives total as 0.

Could you please explain.

Expression-->    Sum({<Month={"$(=Aggr(max(Month),Plan))"}>}Lives)

[swuehl]

For several possible Plan values,

$(=Aggr(max(Month),Plan))

will not return an unambiguous value (try =Aggr(max(Month),Plan)  in a text box).