Using summary statistics functions in calculated field of pivot tables

Jubinell

Board Regular
Joined
Jan 17, 2008
Messages
166
Hi,

I currently have a pivot table that returns min(date) and max(date) for each by-group 'Name.' So it looks something like this:


Name Min of Date Max of Date
John 1/1/2000 9/1/2000
Mary 4/1/1999 3/1/2000
Chris 2/1/2000 6/1/2006

Now I would like to create a new field that returns the difference between these two dates for each name. However Min of Date and Max of Date do not show up under Field List when I go into Calculated Field. How could I do it and still stay within the pivot table (i.e. not create a formula outside it)?

Thank you in advance for your help.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,000
Just wanted to let you know that you're not abandoned. I'm not figuring out a nice way to do this either. Will play a bit more - but thus far I too am striking out.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
fwiw i don't think you can do it within the pivot given a source data set that only has 2 variables (name | date). closest you'd get would probably be a data query
 

Jubinell

Board Regular
Joined
Jan 17, 2008
Messages
166
Thank you guys for your inputs. Alas I don't think there's a way to do this. I ended up creating this variable in the underlying data and parsing it through.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,193
Hi,

A little bit like Paddy wrote, do the work in SQL in between the source data and the pivot table. SQL as below is one way. Assumes headers of 'Name' and 'Date' and source data has (not dynamic) defined name 'source'.

Close the data file. From a new file via menu data, new database query, import external data. Excel files, select the data file then the source data continue until the 'finish' step and take the option to go to MS Query. Hit the SQL button and edit the SQL text to be like below. Hit the SQL button again and then the 'open door' icon to exit MS Query, take the pivot table option. You can now open the source file and move the resultant worksheet & PT back into the source file if you like. So, there are no changes to the source data and the new field appears in the pivot table.

There are lots of possible variations on this approach: this is just one way.

HTH, Fazza

Code:
SELECT Name, `Min of Date`, `Max of Date`, `Max of Date` - `Min of Date` AS [Difference]
FROM (
SELECT Name, Min(Date) AS [Min of Date], Max(Date) AS [Max of Date]
FROM source
GROUP BY Name)
 

Forum statistics

Threads
1,082,639
Messages
5,366,708
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top