Pivot Table [Delta] Field

[thedr9wningman]

Hi, I'm trying to do something I think of as very simple, but I cannot seem to figure it out. I'm doing a comparison of values (so the same field) with different attributes over time.

I'm simply trying to calculate this delta field using fields so that I can dynamically generate the differences. I cannot for the life of me figure out how to do this other than using getpivotdata().

The date columns are called "date of update" and can be various longitudinal values (values/versions over time). Other than that, I'm using a summation of the 'value' field, which is simply the values in a table summed up by the "era" (the row labels that map to the time periods). How do I make an item or field (I don't understand the difference) that is simply value_5/2-value_8/21=delta? (another way to say it is [value_{date of update A}]-[value_{date of update B}]=delta)

Example (sorry for the rotten formatting, I'm doing the best I can):

Sum of Value	Column Labels		
Row Labels	    5/2/2013	8/21/2013	Delta
2004 - 2009	4.50	        1.50	           -3.00
2010 - 2014	4.59	        4.59	
2015 - 2019		        0.00	
2020 - 2024		        0.00	
2025 - 2029		        0.00	
Grand Total		9.09	        6.09

Data is structured as such...

Year	Date of update	Value		Status			era
2008	8/21/2013		1.00	Current from model	2004 - 2009
2009	8/21/2013		0.09	Current from model	2004 - 2009
2010	8/21/2013		0.88	Current from model	2010 - 2014
...
2008	5/2/2013		1.00	Historical value	2004 - 2009
2009	5/2/2013		0.09  	Historical value	2004 - 2009

Thanks for any assistance.

-Feeling thick.

 

[Jerry Sullivan]

Hi up in rainy Portland!

You can do what you describe using a Calculated Item (not a Calculated Field).

This article provides a good overview....

The Microsoft Office Blog

For your purpose, you could make a Calculate Item "Delta" with the formula:
='Date of update'[-1] -'Date of update'[-2]

Make sure to click on one of your "Date of update" headers before clicking the "Fields Items & Sets" button from the Ribbon.

 

[thedr9wningman]

Hi Jerry:

Thanks for the response, but the link is not working.

Group Not Found The requested Group cannot be found.

The login procedure for that site was onerous and difficult as well...

That said, I did find something on that blog that helped. I'm now able to select differing items and perform calculations on them.

Thanks for pointing me in the right direction! I seriously looked for over an hour!
Excel Blog - PivotTables: Calculated Items

 

[Jerry Sullivan]

Oops....My fault for for bad link. You shouldn't have to sign in to access. Try this instead...

Excel Blog - PivotTables: Calculated Items