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

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

• ###### 1. Re: How to create a CASE statement that will calculate % of total

What's the actual question? I see you have an IF statement here, is that different to the case statement you're trying to build?

I really think you'd benefit from creating a sample and uploading a Tableau Packaged Workbook.

• ###### 2. Re: How to create a CASE statement that will calculate % of total

Hey Tom - Question was how to create a CASE statement to return a % of total based on a certain delivery type.  I have the IF statement as my starting point, but wasn't giving me the results I was looking for.

I have since created 4 calculations - one to return # of c-sections, one to return # of vaginal deliveries, one to calculate the % of c-sections based on total deliveries and then the last one to compare against the parameters.

I've attached the basic data I'm working with.  My goal is to have Year then a column for the c-section % for the year and then a KPI indicator if it is within a certain range.

Ranges:  Goal - 18%; Target - 12%; Reach - 8%