Oldest or Newest Date in Pivot Table
Sometimes you'll want to get the oldest or newest activity out of Projector reports. By default, Projector dates are typically treated as a column field in Excel. That means they are used to categorize things like hours into columns. But we can turn dates into Value fields and apply Min or Max to get oldest and most recent dates. For example, you might want to know the last month a resource was ever scheduled. This page will show you how.
Tip - if you are looking at Actuals data and are interested in day level granularity, then some reports have a row field called Work Date. This will be a time card's work date. By using this row field here rather than a column field, you can avoid the annoying popup messages about adding items to the pivot table.
Finding Min or Max Date
Follow these steps:
- Create your report so that it searches for the data you are interested in. For example, scheduled hours.
- On the Column fields tab set the date granularity you need. For example, if you are interested in the last month worked, choose Month.
- Run the report
- Open the report. You may get messages about the table being too wide. This is especially likely to occur if you used a large date range or used Day granularity. Do not worry about the warnings, you can just "Ok" through them. If there are a lot and this is annoying, you could reduce the number of row/page fields you have or open as CSV instead. Then self-pivot the data.
- Once you have the pivot table open, right click anywhere in the pivot table and choose Show Field List.
- This pops up a pane on the right side of Excel with your various report fields. We'll be working in the lower section which has four quadrants.
- Optionally remove any fields in the VALUES quadrant by dragging and dropping them into the top half of the flyout.
- Then in COLUMNS quadrant drag your date type (year, month, day) to VALUES
- Click your date type and a popup appears. Choose Value Field Settings
- Choose Max or Min
- You are done!
