I need to set-up my pivot table to be able to group the data by quarter, and drill down on a quarter by quarter analysis if selected. The image attached has no data but will give the idea of what I am after. The source data has names (where the A, B, C, D, E, F, G are located) and Months.
Question being, how can I set-up my pivot table to include the below Quarterly schedule
Jan-Mar Q1
Apr-Jun Q2
Jul-Sep Q3
Oct-Dec Q4
Now the header Quarter at the top is can be filtered to only display a specific Quarter, and the screenshot shows month to month production, but the Quarters have a plus sign beside them and that can be clicked to only display data for the actual quarter.
Question being, what sort of witch craft and wizardry was used to set-up the pivot table to have this option and how do I replicate?
You'll need to ensure that your source data is rigged up to support this approach.
For example if your source data is regional sales by sale date, and your pivot table should show this by quarter, your source data should look something like this;
Col A | Col B | Col C | Col D
Date | Value | Region | Quarter (helper)
1/1/16 | £1000 | North | ="Q"&ROUNDUP(MONTH(A2)/3,0)&" '"&TEXT(A2,"YY")
Then when you include the helper column in your pivot table you can use it to group your dates;
