topic Re: Aggr(Sum) Function in Alternate States in QlikView

Aggr(Sum) Function in Alternate States

byrnel0586 — Wed, 25 Jul 2018 18:43:02 GMT

Hi all,

I have an expression that is currently working for my needs based on what is currently selected. But now I am needing to make a comparison of this expression against other selections using alternate states - which I have defined as "Group1" & "Group2". I have seen some simple expression examples, but my calculation appears more complex and I am unsure how to begin modifying it for use in alternate states.

Any help is much appreciated. Thank you.

=if(num(ReportDate) < '43312', AGGR(IF(num#(SUM(NUMERATOR)/SUM(DENOMINATOR))>=num#(PtileBeg) and num#(SUM(NUMERATOR)/SUM(DENOMINATOR))<= num#(PtileEnd)
,Star),Star,MeasureID),
AGGR(IF(num#(round(SUM(NUMERATOR)/SUM(DENOMINATOR),0.01))>=num#(PtileBeg) and num#(round(SUM(NUMERATOR)/SUM(DENOMINATOR),0.01))<= num#(PtileEnd)
,Star),Star,MeasureID))

Re: Aggr(Sum) Function in Alternate States

sunny_talwar — Wed, 25 Jul 2018 18:51:19 GMT

May be this

=If(Num(Only({[Group1]} ReportDate)) < '43312',

Only({[Group1]} Aggr(

If(Num#(Sum({[Group1]} NUMERATOR)/Sum({[Group1]} DENOMINATOR)) >= Num#(Only({[Group1]} PtileBeg)) and Num#(Sum({[Group1]} NUMERATOR)/Sum({[Group1]} DENOMINATOR)) <= Num#(Only({[Group1]} PtileEnd)), Only({[Group1]} Star)), Star, MeasureID),
Only({[Group1]} Aggr(If(Num#(Round(Sum({[Group1]}NUMERATOR)/Sum({[Group1]}DENOMINATOR), 0.01)) >= Num#(Only({[Group1]} PtileBeg)) and Num#(Round(Sum({[Group1]}NUMERATOR)/Sum({[Group1]}DENOMINATOR),0.01)) <= Num#(Only({[Group1]} PtileEnd)), Only({[Group1]} Star)), Star, MeasureID))

Re: Aggr(Sum) Function in Alternate States

byrnel0586 — Thu, 26 Jul 2018 15:54:35 GMT

Thanks, Sunny. I'm sure this would work, however, I am seeing the no data to display message. I am new to using alternate states. I am wanting to see this calculation in a chart based on a Physician ID # that I have selected and have assigned to the alternate state Group1. Do I need to assign the fields within the calculation to Group1 also, even if they will not be a selection?

My other thought would be to define the calculation as a field in the script first and then use it for the alternate state. Your help is appreciated as always!

Re: Aggr(Sum) Function in Alternate States

byrnel0586 — Thu, 26 Jul 2018 15:58:55 GMT

Actually, aggr isn't able to be used in the script, so defining as a field there doesn't not seem possible

Re: Aggr(Sum) Function in Alternate States

sunny_talwar — Thu, 26 Jul 2018 16:05:23 GMT

Would you be able to share a sample to show the issue?

Re: Aggr(Sum) Function in Alternate States

byrnel0586 — Thu, 26 Jul 2018 16:30:32 GMT

Sure, thank you so much.

Re: Aggr(Sum) Function in Alternate States

sunny_talwar — Thu, 26 Jul 2018 16:42:44 GMT

There was problem with the parenthesis... try now

Re: Aggr(Sum) Function in Alternate States

byrnel0586 — Thu, 26 Jul 2018 17:05:55 GMT

It is working. Thank you again!!