Forum Discussion

kittenmeants's avatar
kittenmeants
Brass Contributor
Nov 13, 2024

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.

 

    • kittenmeants's avatar
      kittenmeants
      Brass 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:

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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.

    • kittenmeants's avatar
      kittenmeants
      Brass 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?

Resources