Splitting my data by month, year and quarter

[heytherejem]

Hi guys,

I have a bunch of financial data (i've attached a section of it). The figures are all calculated either by Index(match functions or summing, based on the month/year column headings so I can't really change row 1 without really messing things up.

I have a pivot table that points to my data and looks lovely, but I want to be able to show using a slicer - years 2016 to 2018, and also quarters, and months - so people can chop and change what they're looking at.

So I might want to look at all Q4 for the last 2 years so I would select Year = ALL and Quarter = 4.
I can't think of a way of doing this without adding summing columns to my data, and having the pivot pick up separate values?

Help!

[sandy666]

is that what you want?

done with PowerQuery and PivotTable

[heytherejem]

Hi Sandy,

Thanks for replying so quickly.
That would be exactly what I want, but I don't know how to break my data the way you have into years, quarters and months. I can't see how you've done it either as your data source isn't shown on the pivot, if you can let me know how to re-create it I would be eternally grateful.

I only have excel to work with :-|

[sandy666]

Sorry but I don't understand what are you talking about

attachment.jpg

[heytherejem]

Sandy ignore the first comment my computer said it was a php and couldn't open then I realised it was xlsx and I renamed it and then it worked

[sandy666]

hope result pivot table works for you too

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

[heytherejem]

Hi Sandy,

Sorry the pivot does work but I still can't see how you've created the year/quarter/month sections.

Jemma

[sandy666]

Did you read post#2 ?

I said it was done with PowerQuery so if you've PowerQuery find Show Queries, dbl click on table on the right side and you'll see steps

if you don't have PowerQuery add-in you need to download and install it (this is free add-in for Ex2013 from MS site)

[heytherejem]

Sadly I don't have power query, and I can't install it on a work computer. I've tried a cheeky workaround by posting my data into Table1 and it doesn't like it!

I'm hoping for a solution that I can work with in Excel 2016 - I don't have a 'date' attribute, the only date I have is the month and year the value relates to. And my boss wants to be able to chop and change the information so we don't have to select or deselect the relevant pivot columns (of which there are 25 columns Jan 16 to Jan 18 and this will go up as we add more months) so if there is any way I can assign each month to a year, and a quarter, and then if I want to just see 'Q4' only October, November and December show?

Maybe I will have to split the data from month-year and add these attributes as rows...

[sandy666]

Ex2016 (365) has PowerQuery built-in by default so I hope it will work for you with it

table for PivotTable looks like this:

qtable.jpg

Options:

• upgrade MS Office at work to 2016 or higher or
• get permission to install PoqwerQuery/PowerPivot

[heytherejem]

Thanks Sandy, I will ask for this tomorrow! It looks like I need this!! You've been a star. Thank you so much!!!!

[sandy666]

Don't forget about PowerPivot add-in not only PowerQuery
or
with 2016 (365) you'll get both very useful things

have a nice day