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
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
