Hi,
Is it possible to filter date in pivot table the same way as in data table?
I would like to filter by year or month, not specific date only.
Hi,
Is it possible to filter date in pivot table the same way as in data table?
I would like to filter by year or month, not specific date only.
attach your file here
Ash
is attached
something like this ?
not exactly.
Actually I need to filter by date in reportfilter field list.
I'd like to have possibility to check whole month.
See attached
only month ?
Not on a terminal with Excel on it, but it sounds to me like you need to add a grouping. you can do this without modifying the data IF! your dates are actual dates and there are no blank/Null values within the data set.
Easiest way to do this is to first drop your dates in the rows box
Now right Click on one of the dates that show up in the actual pivot table and select GROUP
OR
Click on a date and up top in the Analyze section that should be an off color (Redish Pink by default) you will find "GROUP"
A new window will appear in which you can select the level of groupings you want. Let's stick to your request and click on Year, Month and Days
Hit Okay and you will notice new fields are available in your Fields list and will automatically expand in the rows section.
Take them and move them into the "Filter" section and you now can pivot off them... Or make slicers out of them .... OR leave them there and use the pivot table level filters to tick into them.
From here it is a matter of preference and style how you display them and/or where... But this should achieve what I think you are requesting (Again not at a Terminal with Excel at the moment... but you should be able to follow this...)
Cheers
-If you think you are done, Start over - ELeGault
I suggest you add one column for Year and one for Month in your table........... coz grouping will not allow to take the same 'date' column twice in filter.
Grouping does exactly that. When you Group Date and choose Date, Year, Month it will create 3 fields out of Date. This allows you to filter on Just Month, Just Year, Just Date or Date and Month or Year and Month or whatever you can come up with in this wonderful circle... Adding helper columns is expensive as your data grows. If you can avoid it, you absolutely should.
When you drop those GROUPED fields into filter, Ash is correct... you can only put DATE in there once. However!, you will also have Year to drop in there and Month... Test it out (Ash too) you will see that this is efficient and achieves the same thing that making a helper column does without the overhead!
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks