• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table to display dates

G

gwhenning

New Member
I am trying to create a pivot table to display training records. I'd like the pivot table to display expiration dates for each training. (Preferably, the MAX date since the employee may have taken a training multiple times) I would like it to look something like this:


|TRAINING01|TRAINING02|TRAINING03|

Employee01| 1/1/12 | 02/2/12 | 01/10/12|

Employee02| 02/1/12 | 03/1/12 | |


When I try to add the date field to the Values section and set it to Max I get 01/00/00 for every value. Ideally, we will use a conditional format to highlight those trainings that are expiring. The data is pulled from a SQL Database.
 
Gwhenning


Firstly, Welcome to the Chandoo.org forums.


It is working ok for me

[pre]
Code: 
Max of Date	Column Labels
Row Labels	Train 1	Train 2	Train 3	Grand Total
Fred		5/05/2012		5/05/2012
John	18/07/2012			18/07/2012
Kim	8/04/2012			8/04/2012
Steve			9/02/2012	9/02/2012
Grand Total	18/07/2012	5/05/2012	9/02/2012	18/07/2012
[/pre]

Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


You may also want to check that the Dates aren't stored as Text in the DB
 
Aaargh! Thank you Hui. I tried, and tried, and tried to get this to work; Gave up and searched for an answer; Gave up and posted a question here.


Here's what was wrong: My "Expiration Date" was DATEADD(d,DaysToExpire,TrainingDate). Apparently, this is returned as a string, and not a date. As soon as I wrapped that in a convert(datetime, DATEADD(d,DaysToExpire,TrainingDate), 1) my dates started showing up in the pivot table.


Thank you. Sometimes, the obvious is looked over, and over, and over again.
 
You must log in or register to reply here.
Back
Top