topic Re: When using Total, an expression shows same value for dimension. in QlikView

When using Total, an expression shows same value for dimension.

fionna51 — Fri, 18 Nov 2016 13:53:07 GMT

Hi,

Please see my sample application attached.

I wanted to show both current and last year score for each hospital by simple calculation: Score= sum(Numerator * Weight)/sum(Weight) for all measures which a hospital has for the year. Because number of measures may vary each year, I used Total function to get all measures for last year score calculation. Now the expression is:

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

When I selected any hospital, the calculation is correct, if I don't select any, it defaults to all hospitals and gives me same result for all.

If I select any hospital, the result is correct.

I know my use of Total together with Aggr() must exist some problem, please help me to figure it out. Thanks a lot.

Re: When using Total, an expression shows same value for dimension.

settu_periasamy — Fri, 18 Nov 2016 14:22:18 GMT

what about if you remove the 'TOTAL' from the expression?

round(

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

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

,0.1)

Re: When using Total, an expression shows same value for dimension.

stabben23 — Fri, 18 Nov 2016 14:58:58 GMT

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

will help you

Re: When using Total, an expression shows same value for dimension.

fionna51 — Fri, 18 Nov 2016 15:27:11 GMT

Thanks for all of your help.

Re: When using Total, an expression shows same value for dimension.

fionna51 — Fri, 18 Nov 2016 15:27:28 GMT

This is correct answer.

Re: When using Total, an expression shows same value for dimension.

fionna51 — Fri, 18 Nov 2016 15:28:30 GMT

This is not working, for example h1, it has different number of measures for both years, when select 2015, some 2014 measure missing.

thanks a lot.