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

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

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:

Jason

Jonathan Drummey

Ed Kukec

• ###### 1. Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

Hi Jason,

The calculation you highlighted doesn't add up. 567,770/24,220 = 23.44, not 187.5 or 175.

My suggestions are:

- Remove PCU (Orig) from the view, or make it an ATTR(). That way effects on the table calc will be minimized. This can also speed up Tableau, because Tableau is retrieving fewer dimensions and potentially fewer rows from the data source.

- Put COUNT(Beds (based on new PCU)) on Measure Values and validate that it's returning what you expect. (I'm looking at the fields in the  Bed Count v01 formula).

- Put COUNTD(PCU (new)) on Measure Values and validate that it's returning what you expect.

I'm guessing that one of those calcs is returning different results in the Grand Total, but I'm not sure which, and it might be the calc that is inaccurate.

Also, whenever asking for help about table calcs and not supplying a packaged workbook, you must let us know what the settings are for addressing/Compute Using, At the Level, and Restarting Every. Without those, then we're not sure what the table calc is actually computing to generate the results.

Jonathan

• ###### 2. Re: Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

Thanks Jonathan,

567,770/24,220 = 23.44 ... * 8 = 187.5  (forgot to add the * countd(PCU)) .. my bad.

I've cleaned out the sensitive data and attached the packaged workbook.

The goal is to get the subtotal of 175 and a Grand Total of 367 (I've seen your blog post on Totals in 8.1, but it doesn't seem to apply to subtotals)

Jason

• ###### 3. Re: Re: Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

I got the # of Beds to work using the new Grand Total features in v8.1 (changed total to Total using SUM) ...

But now the Occupancy calculation doesn't seem right (total using Average):

158.33/175.00 = 90.47% .. but Tableau shows 88.31%

Jason

• ###### 4. Re: Re: Re: Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

Ok, I can see where this is a bit challenging...the Max Beds calc is at a higher level of detail, then you're bringing it down into the lower level of detail for the Census, then the subtotals and grand totals do their own computations at a higher level of detail and somehow we have to get everything to work in the same view.

For the Max Beds, you're using the aggregation MAX(Max Beds) with a two-pass total using Sum, which means what you're seeing in the subtotal row for Max Beds is effectively WINDOW_SUM(MAX(Max Beds)). That is not the calc being used for Occupancy, so that's why the subtotal Census/subtotal Max Beds doesn't line up.

The Occupancy calc is a row-level calc using Census/Max Beds, and has the SUM() aggregation applied. So what it's doing is summing up all the row-level Occupancies to get the final rate, and because of how your data is laid out that works ok in the detail rows, but the calcs can break in the total rows because those computations are occuring at a higher level of aggregation. Also, when you are creating the Subtotal using the Average two-pass total, that's not computing a true average, that would be something a little different, the average is the average of all the Occupancy values.

I solved this in the attached workbook using the duplicate dimension technique outlined in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/. That way we have the level of detail inside the subtotal computation.

To be more explicit about what Tableau is doing: The two-pass totals are *only* for the display of the total and subtotal rows, they are not used in other calculations. So if you want to use the value of a two-pass total in another calc, you have to ignore the two-pass total and build your own calculations that return those results, which requires awareness of the level of detail in the detail rows and subtotal/grandtotal computations.

Jonathan

[woops, forgot the attachment]

• ###### 5. Re: Re: Re: Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

Jonathan,

Was there suppose-to be an attachment? I'll re-read the blog post for a third time

Jason

Thanks ... it will take a couple of hours and maybe a good night sleep to get my head around this one.

• ###### 6. Re: Hospital Census/Bed/Occupancy via Subtotals - SumProduct - should I blend?

Sorry about that, you should see it now.

On Tue, Nov 26, 2013 at 3:16 PM, Jason Scarlett <