topic Re: Sum based on aggregation of multiple dimension. in QlikView

Sum based on aggregation of multiple dimension.

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Sum based on aggregation of multiple dimension.

swuehl — Tue, 19 Jan 2016 15:26:13 GMT

Maybe something like

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

Re: Sum based on aggregation of multiple dimension.

sunny_talwar — Tue, 19 Jan 2016 15:31:58 GMT

May be this:

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

Re: Sum based on aggregation of multiple dimension.

Thu, 21 Jan 2016 13:56:25 GMT

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)

Re: Sum based on aggregation of multiple dimension.

swuehl — Thu, 21 Jan 2016 18:33:06 GMT

For several possible Plan values,

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

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