1 Reply Latest reply: Aug 31, 2013 4:34 PM by Lilian Wolfe RSS

    Display multiple if statements results in one column

      I would like to display the results of the following statements in one column (PercentileRank) on a table, how do I combine them into one set analysis statement?

       

      =if(Sum(TotalCharge)>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9),'90')

       
      =if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9)
      and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8),'80')

       
      =if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8)
      and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7),'70')


      =if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7)
      and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6),'60')

       

       

      The results for this field will be 90, 80, 70, 60

      This is similar to case when statements in SQL.. but I am not sure what the right format is to work in set analysis.  Thank you in advance for your help.

        • Re: Display multiple if statements results in one column

          Nevermind.. I figure it out.. here is the correct answer just in case anybody else is interested

           

           

          // 90thPercentileRank

           

          =if(Sum(TotalCharge)>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9),'90',
          // 80thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8),'80',
          // // 70thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7),'70',
          // 60thPercentilRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6),'60',
          // 50thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5),'50',
          // 40thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4),'40',
          // 30thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3),'30',
          // 20thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2),'20',
          // 10thPercentileRank
          if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.1),'10','<10')))))))))