Calculated field

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:

Amending Calculated pivot fields

- 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