PivotTable Trying to Display Values Rather than Sum

[AHACK]

Hi I am trying to use pivot tables to create a series of graphs for a dashboard of sorts.

I have data sets for a series of locations over a long time horizon and will be filtering by date and location.

The issue that I have is the majority (but not all) of the data points are a balance/data point at a point in time rather than a transaction or movement. As such summing the values is incorrect (balance vs transaction).

The data table is laid out as follows.

Year	Quarter	Date	Sector	Market	Stock_P	Vacancy_P	Prime Vacancy (%)	NetAbs_P	Prime Net Face Rent $ sqm p.a.	Prime Incentive %
2000​	4​	36891​	Office	Sydney CBD	493,674​	30,369​	2%​	-20000​	150​	10%​
2001​	1​	36981​	Office	Sydney CBD	493,674​	23,306​	3%​	23000​	155​	10%​
2001​	2​	37072​	Office	Sydney CBD	493,674​	38,669​	4%​	15000​	155​	12%​
2001​	3​	37164​	Office	Sydney CBD	493,674​	42,846​	2%​	4000​	157​	10%​
2001​	4​	37256​	Office	Sydney CBD	493,674​	46,163​	3%​	-3000​	158​	14%​
2002​	1​	37346​	Office	Sydney CBD	493,674​	42,538​	4%​	2500​	160​	10%​

I would like to be able to show the graphs either in full quarterly detail over several years or on an annual basis which would mean summing some information NetAbs (which is a movement per period) and taking the last available value for each annual period for balance data points.

Is this possible or will I have to use the new dynamic array functions to try and create a series of calculated rows of data etc? It seems like PIVOT tables should be perfect but I cant get it to work

Any help is greatly appreciated.

 

[alansidman]

Using the data shown, mock up what you would like your Pivot Table to look like. Also, please update your profile to reflect the version of EXCEL you are using. This will determine the correct solution as different versions have different functionality.

 

[AHACK]

Hi Alan, thanks for coming back. I think I am going to need to create additional data series that ascertain movement in stock between current period and prior period as the compare functionality of the pivot table misses the first quarter in each year. See below.

Row Labels	Sum of Net Abs Total	Sum of Total Market Vacancy	Sum of Stock Total
2011
Mar-2011	16,776​	7.5%​
Jun-2011	30,092​	8.1%​	64,700​
Sep-2011	15,754​	8.5%​	42,360​
Dec-2011	11,535​	8.8%​	25,270​
2012
Mar-2012	-15,840​	9.1%​
Jun-2012	948​	9.1%​	-473​
Sep-2012	-6,524​	9.2%​	0​
Dec-2012	-9,252​	9.0%​	-19,000​

This leads to errors in the data as the Stock Total can change from Dec to Mar periods.

Also if I remove the quarterly periods and run the show values as difference of Year & Previous the calculation is incorrect.

I think the easiest thing to do would be just create some additional helper columns where I calculate movement. (The issue I have with that is that there are multiple locations and sectors so I cant just take the current value less the prior value).

Any way thanks for the help.

 

[alansidman]

I don't understand how the table in your post #3 relates to your post in #1. How do you expect me to understand how one relates to the other when the data does not?

 

[AHACK]

Apologies, I expanded the example data and added variance so that there were changes in the data.

The whole set of tables is below.

As mentioned I believe that the only way to complete the task is to update the data to include various calculated columns tracking movement movement between periods. Then use the standard sum methodology operation of pivot tables to aggregate the data over time series.

Year	Quarter	Date	Sector	Market	Stock_P	Vacancy_P	Prime Vacancy (%)	NetAbs_P	Prime Net Face Rent $ sqm p.a.	Prime Incentive %
2000​	4​	Dec-2000​	Office	Sydney CBD	493,674​	30,369​	6.2%​	(20,000)​	150​	10%​
2001​	1​	Mar-2001​	Office	Sydney CBD	500,000​	23,306​	4.7%​	23,000​	155​	10%​
2001​	2​	Jun-2001​	Office	Sydney CBD	500,000​	38,669​	7.7%​	15,000​	155​	12%​
2001​	3​	Sep-2001​	Office	Sydney CBD	503,000​	42,846​	8.5%​	4,000​	157​	10%​
2001​	4​	Dec-2001​	Office	Sydney CBD	503,000​	46,163​	9.2%​	(3,000)​	158​	14%​
2002​	1​	Mar-2002​	Office	Sydney CBD	503,000​	42,538​	8.5%​	2,500​	160​	10%​
2002​	2​	Jun-2002​	Office	Sydney CBD	555,000​	38,150​	6.9%​	15,000​	160​	10%​
2002​	3​	Sep-2002​	Office	Sydney CBD	555,000​	40,000​	7.2%​	13,500​	160​	10%​
2002​	4​	Dec-2002​	Office	Sydney CBD	535,000​	37,543​	7.0%​	45,000​	162​	10%​
Qrtly Amounts
Row Labels				Stock	Stock Move	Vacancy	Prime Vacancy (%)	Net Abs
2000​
2000​	4​				493,674​	493,674​	30,369​	6.2%​	(20,000)​
2001​	1​				500,000​	6,326​	23,306​	4.7%​	23,000​
2001​	2​				500,000​	–​	38,669​	7.7%​	15,000​
2001​	3​				503,000​	3,000​	42,846​	8.5%​	4,000​
2001​	4​				503,000​	–​	46,163​	9.2%​	(3,000)​
2002​	1​				503,000​	–​	42,538​	8.5%​	2,500​
2002​	2​				555,000​	52,000​	38,150​	6.9%​	15,000​
2002​	3​				555,000​	–​	40,000​	7.2%​	13,500​
2002​	4​				535,000​	(20,000)​	37,543​	7.0%​	45,000​
Annual Amounts
2000​					493,674​		30,369​	6.2%​	(20,000)​
2001​					503,000​	9,326​	46,163​	9.2%​	39,000​
2002​					535,000​	32,000​	37,543​	7.0%​	76,000​

 

[alansidman]

I apologize, but I have no idea what you are trying to do. I am going to leave this for someone who may have a better understanding. Good Luck.

 

[AHACK]

Ok all, my explanation sucks. Apologies. Let me try again.

I am trying to create a dashboard based on a time series of real estate information. There will be a number of different charts which look at data over time and may be filtered for different date ranges etc.

The underlying data I have is a series of individual quarterly records spread over 20 years. I have records for multiple locations and property types. I have stored all of the information in a large single table with rows representing a single quarters information over multiple years. It feels like pivot tables would be the most dynamic way of analysing the data through charts.

My issue is that the native operation of pivot tables is to sum (or perform some calculation) all of the underlying records to generate totals (ie individual transactions over time). In doing this the pivot table can aggregate time periods or display granular information (down to quarterly in my case). Whilst this is perfect for data represented as transactions/movements per period, it doesnt work when looking at balances per period.

Example:
My NetAbs data above is based on changes in the period. This data point works perfectly when summing up quarterly records to generate an annual total.
My Stock_P value is a balance which is can change each period (+/-). As this is a balance summing it presents an incorrect number (eg the balance may be 500,000 for each of the four quarters of a year, the pivot table when viewed at its most granular level represents 4 records each of 500,000 however, when you group as years to chart it shows 2,000,000 which is incorrect. It should still show 500,000 as the balance at Q4)
Prime Vacancy (%) is another case where summing up the four quarters is incorrect. It should just show the balance (record) from the last quarter (date) in the year

I dont know how to get the pivot table to sum fields where the data is setup as transactions, whilst just returning the data point for other fields where the data is based on updated balances per period. Is there a way to force the pivot table just to pick up the underlying balance (data) on an annual basis rather than trying to calculate it through a sum of the quarters?

One work around I thought of was creating some additional calculated columns in the data to generate "movement" transactions for the balances over time and then aggregate these (this feels clunky and open to error).
Another option was to use the Display as Difference function on the pivot table to calculate the movement in the balance data points between periods. The issue here is that it seems to drop off the Q1 difference calculations for the start of each year leading to cumulative data error.

I hope I have managed to explain it more succinctly and convey the issue that I am having.