topic Re: Sum Aggr Expression in QlikView

Sum Aggr Expression

byrnel0586 — Thu, 07 Jul 2016 14:02:16 GMT

Hi,

I'm trying to recreate an expression with a variable I created. This is the expression I was previously using and it worked:

=SUM({<Year = {2017}, Month>}Aggr(IF(
SUM({<Year = {2017}, Month>}NUMERATOR)/SUM({<Year = {2017}, Month>}DENOMINATOR)>= Only({<Year = {2017}, Month>}C_PtileBeg) and
SUM({<Year = {2017}, Month>}NUMERATOR)/SUM({<Year = {2017}, Month>}DENOMINATOR)<= Only({<Year = {2017}, Month>}C_PtileEnd), Only({<Year = {2017}, Month>}C_Star)) *
Only({<Year = {2017}, Month>}Weight), C_Star,_MeasureYearKey,Month))
/
SUM({<Year = {2017}, Month>}Weight)

This is my new expression using the variable I created and it is not working:

=SUM({<Year = {2017}, Month>}Aggr(Only({<Year = {2017}, Month>}$(CStar_PCR)) * Only({<Year = {2017}, Month>}Weight)
,C_Star,_MeasureYearKey,Month))
/
SUM({<Year = {2017}, Month>}Weight)

Can someone please tell me what is wrong with the new one?

Thanks.

Re: Sum Aggr Expression

sunny_talwar — Thu, 07 Jul 2016 14:20:08 GMT

How do you define CStar_PCR variable? Can you may be share a sample to explore this?

Re: Sum Aggr Expression

byrnel0586 — Thu, 07 Jul 2016 14:22:37 GMT

Sure, here is the variable definition:

if(match(MeasureID,'PCR2') and match(Year,'2016'), Avg(Aggr(IF(($(OfficialRate)*12.85)/100>=C_PtileBeg and (($(OfficialRate)*12.85)/100)<= C_PtileEnd,C_Star),C_Star,_MeasureYearKey)),
if(match(MeasureID,'PCR2') and match(Year,'2017'), Avg(Aggr(IF(($(OfficialRate)*12.99)/100>=C_PtileBeg and (($(OfficialRate)*12.99)/100)<= C_PtileEnd,C_Star),C_Star,_MeasureYearKey)),

Avg(aggr(IF(SUM(NUMERATOR)/SUM(DENOMINATOR)>=C_PtileBeg and SUM(NUMERATOR)/SUM(DENOMINATOR)<= C_PtileEnd,C_Star),C_Star,_MeasureYearKey))))