6 Replies Latest reply on Nov 26, 2013 12:24 PM by Jonathan Drummey

    Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

    Jason Scarlett

      I'm going in circles on this one.

       

      I have hospital patient data that consists of one record per PCU (patient care unit) that a patient is in (ADT data).

      1. I am using a Parameter to alter the PCU of the patient to a "new" location.  -- DONE
      2. I have calculated the "census' on each unit by summing the patient days -- DONE
      3. I am trying to display the number of beds on each unit WITH a subtotal -- FAIL
        • The data for the # of beds on a PCU is attached at the record level (i.e. each row has a "# of Beds in PCU" column)
        • I have created a calculation that "nulls" out the "# of Beds in PCU" if the patient has been moved (based on parameter)
        • I can't seem to get a SumProduct to work across the Pane (i.e. in excel notation =SUMPRODUCT(sum_beds,1/count_beds)). This works on the cell level, but at the subtotal level Tableau is summing the entire column and then multiplying ... I want Tableau to sum the cell and multiply it and then sum all of those results
      4. Once I have the Bed Count, I 'should' be able to figure out the occupancy calculation. -- TBD

       

      Due to privacy concerns, I can't post an example workbook, but will try to mock something up if requested.

      I have also tried blending the data and solved some of the issues using this solution (http://community.tableau.com/thread/113661) ... but I ran into another with the Blended Bed counts (more on that if the suggestion is to use blended data).

       

      Here is a picture of the table:SumProduct.jpg

       

      Jason

       

      Jonathan Drummey

      Ed Kukec