2 Replies Latest reply on Dec 31, 2012 12:33 PM by Julia Hennelly

# Multiple thresholds for different cells on one worksheet

Not sure how best to explain this and am hoping it is a simple calculation.  I am looking at a group of associates who are either experienced or inexperienced and are taking a baseline or reassessment.  The result is either acceptable or unacceptable.  I am then looking at percentages across a hospital of acceptable versus unacceptable.  I have 4 parameters as the target rate is different depending on experience/inexperience/baseline/reassessment.  I then want to colour each cell based on being above or below the appropriate target.  Second issue is for acceptable you need to be above target and for unacceptable you need to be below target.  Right now I have the following calculation:

IF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) < [Target Experienced Baseline]/100 THEN 'Below Target'

ELSEIF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) >= [Target Experienced Baseline]/100 THEN 'Above Target'

else 'Below Target'

END

I need to add to this so if looking at acceptable then this is fine but for unacceptable I need the opposite.  I thought the following might do it:

if [Acceptable] = 'Acceptable' then

IF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) < [Target Experienced Baseline]/100 THEN 'Below Target'

ELSEIF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) >= [Target Experienced Baseline]/100 THEN 'Above Target'

elseif

COUNTD([Employee]) / TOTAL(COUNTD([Employee])) >= [Target Experienced Baseline]/100 THEN 'Below Target'

ELSEIF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) < [Target Experienced Baseline]/100 THEN 'Above Target'

END

END

but gets an error about mixing aggregate and non aggregate...

I then tried this but that does not work as I get a bunch of null values in the unacceptable quadrant

if max([Acceptable]) = 'Acceptable' then

IF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) < [Target Experienced Baseline]/100 THEN 'Below Target'

ELSEIF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) >= [Target Experienced Baseline]/100 THEN 'Above Target'

elseif max([Acceptable]) = 'UnAcceptable' then

if COUNTD([Employee]) / TOTAL(COUNTD([Employee])) >= [Target Experienced Baseline]/100 THEN 'Below Target'

ELSEIF COUNTD([Employee]) / TOTAL(COUNTD([Employee])) < [Target Experienced Baseline]/100 THEN 'Above Target'

END

END

end

I then need to take this a step further and basically look at the value in each cell and determine where it fits for its target.  Do I need separate worksheets on a dashboard to do this?

Julia

• ###### 1. Re: Multiple thresholds for different cells on one worksheet

Hi Julia,

I'm a bit confused about the above/below target as it applies to unacceptable, however in the attached I made a calc that basically works and should only need tweaking for what needs to be a  <=/< or >=/> to fit your requirements. It's a set of nested CASE statements, these aren't necessarily the fastest but they do make the calc pretty explicit and also serve the function of returning Null when no values are assigned.

You can see in the "workout" worksheet that there are no Null values, however in the "crosstab" worksheet there are Null values. This is because Tableau is doing domain completion, where it generates a cell for every combination of the discrete pills on Rows & Columns. Those cells have no data to generate a value, so the Threshold Calc field is returning Null and generating an extra Color in the color legend. In cases like this, I typically just alias that to "No Value" and let Tableau do its thing.

I also work with hospital data, one thing I've switched to in cases like this where the targets for given measures can be greater than or less than measure values and I'm showing both on the same window is to use "met target" and "did not meet target" instead of "above/below".

Finally, the view you set up is using green/red for a color scheme with no other indicator. The several % of the population that is red/green color blind would not be able to make out the difference, I suggest using something like black/red, it's easier to read (since the view would only have two colors, instead of black&green&red) and doesn't have that effect.

Jonathan

• ###### 2. Re: Multiple thresholds for different cells on one worksheet

Perfect - thank you!  Agreed about the colours - just need to convince the user who is stuck on wanting red and green???  One quick question - what do you mean about aliasing the null to no value and letting tableau do its thing?  Can I eliminate null from showing in the legend?

Thanks,

Julia