Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
fionna51
Creator
Creator

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

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.

Pic1.PNG

If I select any hospital, the result is correct.

pic2.PNG

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

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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

like

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)

View solution in original post

5 Replies
settu_periasamy
Master III
Master III

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

like

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)

View solution in original post

stabben23
Master
Master

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

fionna51
Creator
Creator
Author

Thanks for all of your help.

fionna51
Creator
Creator
Author

This is correct answer.

fionna51
Creator
Creator
Author

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.