Hi,
First time poster here, so please be gentle if I inadvertently do something wrong.
I have a pivot that is used to present a quarterly overview of daily information collected over several years. The simplified file (attached) uses a pivot table to group the data by the year and quarter of the related date, shows the average value of the quarter in the first field, followed by a second field showing the % difference between that average value for the period and the previous quarter's average value.
For some reason, the pivot only considers quarters within the same year as previous to one another, so does not calculate a % difference from previous for Q1 of each year (as Q4 of the previous year is treated as being in a different group).
Can this problem with pivot tables be overcome or do I need to build a manual presentation table rather than using the pivot table?
Many thanks for any help in advance.
Hi Jonathan
What is your expected result?
Have you tried selecting the Base Field as Years instead on Date?
Hi SunnyKow,
Thanks for your response.
My expectation was that I would have a % difference calculated for every quarter other than the first in the table, but the table excludes the calculation for the first quarter of every calendar year - not just the first quarter of data. For example I would expect the value next to Q2 2018 to show the % difference from Q1 2018, then the following row to show Q3 vs Q2 and so on. However, where I would expect Q1 2019 vs Q4 2018 there is no result.
I have tried changing the base field to Years, but this simply means you get a % difference on the average values for each year. then if you add quarters, you get a comparison between the same quarter in different years, not adjacent quarters. (i.e. Q1 2019 is compared to Q1 2018 instead of Q4 2018.)
I hope that makes the challenge clearer.
Thanks.
I tried some other solutions in the pivot table itself, but encountered the same issues. So maybe it's indeed something that can't be solved this way and only by something with a helper column or such. Is it worth for you trying figuring out alternative solutions, or is the answer 'it can't be done' enough?
Frans
Hi Jonathan
You will need to create a helper column in your data.
Refer attachment for an example.
Good luck.
Sunny
