Pivot Table count dates older than x days

michaelg

New Member
Joined
Mar 31, 2005
Messages
24
Team,

I have a table with columns...

Opened
- date a ticket was opened
- format = dd/mm/yyyy

Group
- name of a group ticket was assigned to
- format = [text string]

Active
- flags whether the ticket status is still open
- format = true/false

I currently have a pivot table set up, where

Columns =
- Opened (Year)
- Opened (Month)
- Opened

Rows =
- Group
- Active

Values =
- Count of Opened

The rows in the pivot table have a filter for "Active" where only "TRUE" values are selected.

Table works fine, but I would like to only count active records that were opened more than 10 days ago (to report aged record totals outstanding against support groups)

Note: I am aware of the Date Filter "is before", however this requires a specific date, whereas I would like older than 10 days from "today".

Note: the data is being cut/pasted from another source into a sheet on the spreadsheet, to keep things simple I do not want to include a helper column where data is being dumped in the event it get wiped or corrupted.

Note: I have PowerPivot activated, but have little experience with it and I am familiar with using VBA in excel.

I'm guessing, the options are;

1 - helper column that creates a true/false test of opened date older than today+10 (do not wish to implement this solution as stated above).

2 - some alternative to the "is before" data filter that I have yet to discover(?)

3 - a VBA function that applies a filter to the pivot table, please note the data will be used for other charts and stuff so cannot be directly manipulated.

4 - a powerpivot measure/calculated column that filters the data as required(?) - as I have no experience with powerpivot I would ask that any solution is dumbed down into novice level steps of implementation.

Your assistance with this challenge is most appreciated.

Regards
Michael
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,118,658
Messages
5,573,451
Members
412,529
Latest member
cTatch
Top