Using summary statistics functions in calculated field of pivot tables

[Jubinell]

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]

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]

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]

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]

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

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)