Forum Discussion
Pivot Table: Date Filters are not Condensed
In this one random excel workbook I am trying to modify, the dates in the pivot table are not condensed. They are all over the place. Usually, when I create a pivot table, the dates are sub-sectioned into year, month, and then the date. This just has random dates.
I've tried converting the RawData to "Short Date", Data > "text to Columns", I made sure there were no blanks in the raw data, etc. I drug the "Survey Date" to rows in the pivot table and that shows grouped as Months and Years, but that fix does not work as well. It has a weird 2022 populating.
I'm lost.
The raw data was also causing issues. When I filtered for previous years (2022, and 2023) they would each show up with 2024 data. I changed the header, what was a long question, to just "Survey Date", and that fixed that issue. The raw data is being exported from SurveyMonkey.
See attached.
Refresh the pivot table. The 2022 part should disappear.
- kittenmeantsBrass Contributor
Yes, that part will disappear. However, when I put the "Survey date" in the filters, the dates do not condense. We work off of fiscal months and years so it's not just "October" or "2024" that I will need to look at. We are also expected to have a larger amount of data than what is shown, which is why I was looking to trouble shoot why the Filter drop down in the pivot table is not in Year > Month> Day selection format. Just a list of dates. (Also thank you!)
This is what I need for this workbook:
I'd add slicers based on the Years (Survey Date) and Months (Survey Date) fields:
What are your settings here
And why don't to use structured table as the source and not to use data model PivotTable instead of cached PivotTable.
- kittenmeantsBrass Contributor
I have to modify the data quite a bit to get the different analysis numbers I need, and look at fiscal month, fiscal quarter, and fiscal year. A Pivot table is just the easiest way I have found to pull the data, see an inconsistency, and imminently go to that data point.
Should I have the last box checked?
Depends on goals, but better to have unchecked