4 Replies Latest reply on May 11, 2018 1:27 PM by Shinichiro Murakami

    How to write a CASE statement with overlapping/cumulative output categories

    John O'Malley

      The screen shot below shows what I'm trying to create: a display of the % of dentists who have seen AT LEAST __# of patients.

       

      Every dentist has served AT LEAST 1 patient, so the first mark is 100%. But as the panel size increases by 10 patients, the % of dentists who saw that many patients decreases.

       

       

      To make this graph I created separate measures for each column and then put the Measure Names/Values pills in the sheet. Not ideal. But I can't seem to write a single measure that will allow a dentist to be counted in multiple columns. Whenever I tried to create BINS or write a CASE / IF statement, Tableau assigns the dentist to the first category on the list and excludes them from all others. 

       

      Example...

      CASE [# Receiving]

      WHEN >=1 THEN "1"

      WHEN >=10 THEN "10

      WHEN >=20 THEN "20"

      WHEN >=30 THEN "30"

                ...

       

      Is there a way I can calculate a field so that each line in the CASE statement is not exclusive of the ones above it?

       

      It's the cumulative "AT LEAST" feature that's throwing me off. I don't just want a histogram that reports # dentists exclusive to each panel size. I want to be able to show the sharp decrease in participation from 100% as panel size increases.

       

      Using Tableau Public 10.5. Workbook available for download on Tableau Public: