Using summary statistics functions in calculated field of pivot tables

J

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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel Pivot-Table calculated fields with if statement return wrong results
Replies
3
Views
309
RoryA
RoryA
N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
130
Nurces
N
J
  • Solved
Problem with Calculated Fields in a Pivot Table
2
Replies
12
Views
1K
JimRoy
J
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
225
Sergius
S
J
  • Question Question
pivot table pull value if name changes
Replies
1
Views
176
Kaper
Kaper

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top