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.
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.
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
Refresh the pivot table. The 2022 part should disappear.
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:
