+ Reply to Thread
Results 1 to 12 of 12

Splitting my data by month, year and quarter

  1. #1
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Splitting my data by month, year and quarter

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Splitting my data by month, year and quarter

    is that what you want?

    done with PowerQuery and PivotTable
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Splitting my data by month, year and quarter

    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 :-|
    Last edited by heytherejem; 03-14-2018 at 02:10 PM.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Splitting my data by month, year and quarter

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

    attachment.jpg

  5. #5
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Splitting my data by month, year and quarter

    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

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Splitting my data by month, year and quarter

    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.

  7. #7
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Splitting my data by month, year and quarter

    Hi Sandy,

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

    Jemma

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Splitting my data by month, year and quarter

    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)

  9. #9
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Splitting my data by month, year and quarter

    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...

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Splitting my data by month, year and quarter

    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
    Last edited by sandy666; 03-14-2018 at 03:15 PM.

  11. #11
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Splitting my data by month, year and quarter

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

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Splitting my data by month, year and quarter

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-08-2018, 03:47 PM
  2. SALES COMPARISON BY WEEK NUMBER, MONTH, QUARTER AND yEAR
    By faodavid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-30-2017, 02:53 PM
  3. [SOLVED] Locating beginning and end of week, month, quarter or year balance
    By ehollifield in forum Excel General
    Replies: 10
    Last Post: 03-29-2017, 02:00 PM
  4. How to sum data by month, by quarter, by year?
    By kevinSHR in forum Excel General
    Replies: 3
    Last Post: 05-06-2016, 04:06 PM
  5. [SOLVED] Month/quarter/semestre/year calculations
    By pccamara in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2016, 08:37 AM
  6. Expanding 4th quarter by month ONLY for certain year
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-28-2013, 06:13 PM
  7. [SOLVED] maturity schedule fixed inc securities by month, quarter and year
    By maturity schedule in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2006, 07:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1