Excel 2010 Pivot Table Values Total Column Divided By Days In month

[PWD]

Good afternoon, I was trying to check the utilisation of hospital wards ("NurseUnit") by just getting occupancy data for November 2016 (from a Business Objects report from our new supplier's Universe) and pivoting it up. It's just the Ward Name (NurseUnit) in the Row Labels and Count of Encounter ID in Values. I know that I can manually enter a formula in the next column to the right of the PT but as this results in a very specific formula:

=GETPIVOTDATA("MILLENNIUM ENCOUNTER SLICE ID",$G$3,"NurseUnit","Birthing Unit")/30

I can't just copy this down to the end of the column.

I know that I can go down each row and type "= [left arrow] "/30", <CR> but I don't believe there's a way to add a Calculated Field that refers to PT Totals or Sub-Totals. Do I have any 'smart' options? I suppose I could just put:

=H5/30

as that's where the Total column starts.

Many thanks,
D€$

 

[SkipVought]

There's probably a neat way to do that with a PT. I've always looked at PTs as a really great way to get quick aggregations, but have shied away from PTs for more comprehensive customized summarization. So I'd make my own pivot and summarize using SUMPRODUCT().

I'd also calculate the exact days per month via
DATE(YEAR(x),MONTH(x)+1,0)

Or just
=H5/DATE(YEAR(x),MONTH(x)+1,0)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If you are ok with 30 days in every month calculated field will do it, add field formula referring to source field divided by 30 and pivot table will sum it up for each domain.

For Skip's solution extract day from the last day in month date: =H5/DAY(DATE(YEAR(x),MONTH(x)+1,0))


combo

 

[PWD]

Hi Guys I have attached the relevant workbook, it should open on the worksheet 'Pivot Bed Occupancy'. The problem is that my source field is the calculated total column.

Many thanks,
D€$

 

[SkipVought]

Is this ALWAYS going to be ONLY one month's worth of data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...

If so, check out this solution. This Query Table will refresh each time the workbook is opened.

You WILL need to "correct" the connection string via...

Right-click in ResultSet

Select Table > External Data Properties > Connection Properties (Upper right next to Name) > Definition > Connection String -- Make path corrections in TWO places.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, yes this will be run each month, for one month only.

Dang, ODBC Excel Driver Login Failed

'C:\Users\Skip\Downloads\MR78_-_........ is not a valid path​

Many thanks,
D€$

 

[SkipVought]

See my instructions for correcting the path.

Of course, the path could automatically be corrected via event code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]