Pivot Table Count IF

[Keith86]

I need to display basic performance of the recovery department in operating theatres.

Ideally... I'd like to see
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Hour
[/TD]
[TD]Patients
Admitted
[/TD]
[TD]Patients
Discharged
[/TD]
[TD]Avg
Patient LoS
[/TD]
[TD]Patients
In Recovery
[/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]08:00
[/TD]
[TD]10
[/TD]
[TD]8
[/TD]
[TD]01:34:20
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]09:00
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]02:24:04
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]1 hour
increments
...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


The raw data for this, has 1 row per patient, columns including many fields:
LoS (length of stay), Time of Admission - a number of different versions of this field which I've extracted, i.e. Month, Time, Date. Then the same date/time field variations for Time of Discharge.

Does anyone have any suggestions of how I can acheive the top table? I've currently pivotted the data and managed to include the Recovery_In_Hour as the row labels, then in the pivot table values I've done a simple count of patient numbers, and included an average LoS. The joy of using the pivot table means that I can have some slicers to select i.e. specific wards/departments/staff/weekdays.

To show the discharged numbers I've copied this pivot table and changed the row labels to Recovery_discharge_hour. Not elegant, but then I've simply hidden the row labels column and moved the pivot table next to the other one to amke it seem like it's the same table. The slicers change both tables.

However trying to include the cumulative count of patients, in my existing pivot tables is proving challenging. This will be the most meaningful piece of data though.

Does anyone have any suggestions on how I could display this?

 

[alfranco17]

The way I'd do it, you need to add a common field that has both times, so you need to split your data. Here's an example:<p>I added the type for clarity in column G, but you don't really need it.<p>Does this help? <p><p>

<p>Another alternative is to use a PowerPivot, create two linked tables from the same data set. One table has columns A and B, the other has A, C, D. In those two tables, link arrival and discharge times. Create a pivot table, and you will be able to get your report.