Excel: Filter PivotTable

[krets]

I'm having trouble figuring out how to set up my PivotTable to filter by dates. The raw data I'm using has the following column heads:

Key | CellNum | Error Code | AITDate | Severity

The table is set up with Error Code as the column heads and CellNum as the rows. The data is a count of Key.

What I would like to do is be able to filter by a date range. If I add AITDate to the Page Area I can filter for a specific date but not for a range. Is there a way to filter for a date range?

 

[gizmo1973]

I have tried this before and the work around I came up with was to put a date range within the data itself before you Pivot it

ie all dates in January have a new column with a date range of January etc etc

that works but may not be what you are looking for, the easy way to do the range within the data would be the month function

Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[krets]

That's not a bad solution. If all else fails I can go with that. I'd love to have the flexibility of selecting multiple dates if possible though.

 

[SkipVought]

Hi,

Unless you have a DateRange indicator in your source data, you can't specify a range. HOWEVER, you can GROUP your Date Field data into ranges of a fixed nature, using Group & Show detail... > Group - and select the Starting a and By values that you need

If this is not to your suiting, you may need to look at the Database Functions.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Tatertot45]

I run into this issue all the time. The Salesforce i support like to slice, and this was driving me nuts. This won't work if your original data range is too big, but this definitly helps me. I first put in the Date field as column header. then i add the other column data below it. Example: I ahve sales going down the left. Then add "Order.date" to the columns, then "order.sale". I can then use the drop down filter for "Order.date" to select the dates i want to populate. I then take the grand total and give it to sales. You only get 256 columns, so creating broader date tag, like month or year, will help Maximize column usage.
The other thing you can try is to seperate out the date parts, like month and year. Put the month accross the coluns, and year as a subgroup to each key.

Let me know if you have any questions.

 

[SkipVought]

"...This won't work if your original data range is too big,..."

This ABSOLUTELY will work!

You get the warning about not fitting on the sheet -- IGNORE.

Group by Year & Month and you will most likely get your entire range of dates/values displayed.

Group by DAYS - 7 and get grouped by WEEKS.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Tatertot45]

Odd... Whenever i try to drop in more than 255 date instances, i get a message that tells me that i either can drop the first 255, or cancel. that is why i mentioned that if the dates are too wide ranging, my workaround does not work. Skips' method should work fine at anytime. I work with "tech-challenged" endusers, and the drop downs are easy for them to manipulate. It works for me, but may not work fo you.

Apologies to Skip... Was not stating that his method wouldn't work with large date instances, was refering to mine.