Until fairly recently I didn’t realise you could edit user-defined calculated fields in pivot tables. Well I was wrong!
All this time I’ve been taking extra care when creating a calculated field because I didn’t think I would get a second opportunity to correct it.
Yesterday I was doing some preliminary work for some contracts that need to be agreed for next year. I was trying to work out the maximum affordability based on recurrent forecast out-turn for this year plus some growth assumptions less Cost Improvement Programs (or QIPP).
I wanted an additional field in my pivot table that showed the pre-CIP total. I went ahead and created a calculated field where,
PRE-CIP TOTAL = BASELINE + DEMO GROWTH + NON-DEMO GROWTH
At the time we had been working with the assumption that the net of Inflation and Efficiency would be zero so I ignored it. Actually I forgot about it but at the time it didn’t matter.
Now that we have the NHS planning guidance, we know that the net of inflation and efficiency is now 1.1% for next year. So now I need to amend my PRE-CIP Total to be:
PRE-CIP TOTAL = BASELINE + DEMO GROWTH + NON-DEMO GROWTH + INFLATION + EFFICIENCY
Here is the method for amending a calculated pivot field:
- Click on the pivot table - Go to the Analyse area of the ribbon - Select Fields, Items & Sets - Click Calculated Field - Now look for the drop down box next to the Name (the bit I always missed) and select your calculated field - Now you can amend or re-write completely - Click OK to finish