6 Replies Latest reply: Sep 1, 2017 9:38 AM by Linda Pembroke RSS

    Rounding within Set Analysis

    Linda Pembroke

      Hi,

      I am trying to use the rounding function within this set analysis expression and it isn't working correctly. I am not sure if I have the round() in the right place, but I have gotten this to work in my other expressions that do not have set analysis in them. I want to round the final result of the "if not" part of the statement. I also want the rounding to only occur from July forward, but you can see in the trending  that it is rounding all months prior as well.

       

      Any help is appreciated.

       

      =if(num(ReportDate) < '43312', SUM({<Year = {2018}, Month>}Aggr(IF(
      num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
      num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
      Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
      /
      SUM({<Year = {2018}, Month>}RptCardWeight),
      //this begins the "if not" expression
      round(SUM({<Year = {2018}, Month>}Aggr(IF(
      num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
      num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
      Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
      /
      SUM({<Year = {2018}, Month>}RptCardWeight),0.1))

       

        • Re: Rounding within Set Analysis
          Kaushik Solanki

          Hi,

           

          You can use round function before your if statement.

           

          =Round(If.....)

           

          Regards,

          Kaushik Solanki

            • Re: Rounding within Set Analysis
              Linda Pembroke

              Thank you, but where would I end the round function with 0.1? I tried this and now it's not calculating anything for July but isn't rounding the months prior.

               

              =round(if(num(ReportDate) >= '43312',0.1,

              SUM({<Year = {2018}, Month>}Aggr(IF(
              num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
              num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
              Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
              /
              SUM({<Year = {2018}, Month>}RptCardWeight)),

              SUM({<Year = {2018}, Month>}Aggr(IF(
              num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
              num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
              Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
              /
              SUM({<Year = {2018}, Month>}RptCardWeight))

            • Re: Rounding within Set Analysis
              Sunny Talwar

              Have you checked if you if condition is working.... May be try this and see if before july you are able to get 1 and after july it should be 2?

               

              =If(ReportDate < '43312', 1, 2)

                • Re: Rounding within Set Analysis
                  Linda Pembroke

                  Yes, the condition is working in my other expressions. For example:

                   

                  =if(num(ReportDate) < '43312',
                  SUM(Aggr(
                  IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
                  ,
                  Star) * Only(RptCardWeight)
                  ,
                  Star,_MeasureYearKey))
                  /
                  SUM( RptCardWeight),
                  round(SUM(Aggr(
                  IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
                  ,
                  Star) * Only(RptCardWeight)
                  ,
                  Star,_MeasureYearKey))
                  /
                  SUM( RptCardWeight) ,0.1))

                  • Re: Rounding within Set Analysis
                    Linda Pembroke

                    Hi Sunny,

                    You are right, it is something wrong with the condition. Since it is a trending chart using set analysis, this condition only works when selecting the month. I need to create an 'if' using set analysis I think...

                      • Re: Rounding within Set Analysis
                        Linda Pembroke

                        Got it. Thanks.

                         

                        if(Only({<Year = {2018}, Month>}ReportDate) < '43312',SUM({<Year = {2018}, Month>}Aggr(IF(
                        num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
                        num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
                        Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
                        /
                        SUM({<Year = {2018}, Month>}RptCardWeight),

                        round(SUM({<Year = {2018}, Month>}Aggr(IF(
                        num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
                        num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
                        Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
                        /
                        SUM({<Year = {2018}, Month>}RptCardWeight),0.1))