Forum Discussion

ClaudiaM1's avatar
ClaudiaM1
Copper Contributor
Jun 29, 2020
Solved

Excel Pivo table

Hi,   I have a pivo table with two columns with date, start date and end date, I want to group by quarter on end date but the table groups by start date, how do I change it so it groups quarter on ...
excel
  • ClaudiaM1's avatar
    ClaudiaM1
    Copper Contributor
    Jun 30, 2020

    Riny_van_Eekelen Hi Riny!

     

    Thank you! I couldn't figure out what was wrong, all the settings seemed to be correct and since I didn't group the dates in the column area myself I just couldn't figure it out. I guess that adding two new columns with year and month to data source is the best alternative, although it feels so odd that the pivot table can't handle dates being grouped? It feels like I always come across strange things in excel! 🙂

     

    Thanks again!

     

    Ha det bra!!

     

    Kind regards

    /Claudia

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 30, 2020

    ClaudiaM1  I suspect that Excel doesn't like that dates are grouped in the column area and that the the year and quarter components are then dragged to the filter area. As you have mentioned, it totally screws up the PT and graph upon refreshing.

    Therefore, I added two columns to the data that calculate the year and quarter. Then, I rebuilt the PT. Now it remains filters and formatted upon refresh.

     

    I trust you can reformat the PT and rebuild the graph yourself.

     

    Har det bra!

  • ClaudiaM1's avatar
    ClaudiaM1
    Copper Contributor
    Jun 30, 2020

    Riny_van_Eekelen Hi again, sorry to bother you 😉 I wonder why my pivot table changes when I refresh data, It should only update data from data source but no change my output (filter and selection) ??

     

    Regards

     

    /Claudia

Resources