2 Replies Latest reply on Jun 11, 2015 8:28 AM by John Hobby

    How to create a CASE statement that will calculate % of total

    John Hobby

      Howdy,

       

      I'm working with some healthcare stats and am trying to create a KPI worksheet.  In my data, I have a field called 'Delivery Type'.  The values are either Cesarean Delivery or Vaginal Delivery.  I'm viewing this based on total # of deliveries for the Year (but will need to view based on Quarter or Month).  I was able to get the % of total for each delivery type using the quick table calculation.  I've created 3 parameters 'Target', 'Goal', 'Reach'. 

       

      What I would like to do is to have a calculated field that is based on a CASE statement, so that when 'Cesarean' it will generate the % of Total Deliveries (say for the year) and then compare that to the parameter of 'Target' and return the word 'Target' - then compare to Goal and Reach.  I can then use this to assign a KPI shape.

      -----------------------------------------------------

      Current Calculated field '% of C-Section vs Vaginal Deliveries'

       

      if (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) >= [C-Section Target] then 'High'

      elseif (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) < [C-Section Target]

          and (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) >= [C-Section Goal] then 'Target'

      elseif (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) < [C-Section Goal]

          and (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) >= [C-Section Reach] then 'Goal'

      elseif (SUM([Num Encounters]) / TOTAL(SUM([Num Encounters]))) < [C-Section Reach] then 'Reach'

      else 'Missing' end

      -----------------------------------------------------


      Thanks,

      John