4 Replies Latest reply on Oct 5, 2017 7:29 AM by Kyle Willey

    Category-Based Calculations

    Kyle Willey

      Hello all,


      I was hoping someone out there has the answer for this.  I am in the process of migrating my dashboards from Excel format into Tableau.  The area I am having trouble is in making calculations based on the types of units selected as a filter.  I am specifically looking at hospital unit infection rates and comparing them to national standards, but each category of unit has a different risk stratification.  For example, if I have an ICU, a surgical unit, and a rehabilitation unit, the risk is highest in the ICU, then the surgical unit, then the rehab.  If we assume, for instance that the national averages are 3.0, 2.0, and 0.5, respectively, I want to compare my unit's performance to those averages based on if I choose one unit, or an average of the performance if I choose all units.


      Is there a way to categorize the units and then compare my rates to those national averages (O:E ratio)?




        • 1. Re: Category-Based Calculations

          Just FYI, you might have better luck posting in the "general" forums since it sounds like your question is more of a Tableau technical question than healthcare specific.  Though I could be wrong.


          Also, it will probably help your chances if you can post a packaged workbook with dummy data that represents what you're trying to accomplish.  There are some threads around here that show how to anonymize your data if that floats your boat. 

          • 2. Re: Category-Based Calculations
            Hope Stiles

            Sometimes in a case like this I create a secondary data source that would show the national average for each unit.  You can blend the two data sources and then pull in the corresponding national average for that unit and it will aggregate.  If you're working with a relatively small number of units, you can just create a calculated field assigning that national average to each unit, but if you have a lot of them it's easier to do in a separate excel file.



            1 of 1 people found this helpful
            • 3. Re: Category-Based Calculations
              Jonathan Drummey



              This is doable but like Mark said we’d need to see the data and know two additional things:


              - are the benchmarks in this data set or a separate data set?

              - when computing over multiple hospital units do you want an average benchmark or do a weighted average benchmark?



              1 of 1 people found this helpful
              • 4. Re: Category-Based Calculations
                Kyle Willey

                Thanks guys,


                I was able to figure out the bulk of this problem with a little help from a cross-post on the general side of the Community. @Hope Stiles, I did exactly that with a page with the national averages and cross referenced.  And hard coding is out with 46 units, and I would like to scale this up to System level if it works out.  @Jonathan Drummey, you can see that I did a simple average over the units, but I am not really happy with that.  I have 2 thoughts on that: I can do a weighted average, but that requires going to MDX and all sorts of other problems, right? Secondly, I might write a formula that if all units are checked, I can just compare to the known whole hospital SIR and bypass the problem.  The majority of my users are going to care about whole-hospital or their own service line.  I think that, even if we selected all surgical units, the SIR would be a close enough approximation that it would have minimal impact.


                Thoughts on migrating to cubes and MDX over the bypass plan?  Sample data attached.