2 Replies Latest reply: Jul 7, 2016 10:22 AM by Linda Pembroke RSS

    Sum Aggr Expression

    Linda Pembroke

      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

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

            • Re: Sum Aggr Expression
              Linda Pembroke

              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))))