+ Reply to Thread
Results 1 to 4 of 4

Pivot table with custom indexes

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    Timisoara, Romania
    MS-Off Ver
    2016
    Posts
    4

    Pivot table with custom indexes

    I have an Excel datafile where a record consists of:
    Point of Sale Year Quarter Sales
    [Names] 2012 thru 2017 1 thru 4 for each year Cumulative, year-to-date (so for Q1 - the numbers are always only for Q1 of that year; but for Q2 it will be Q1+Q2 for that year; Q3 would be Q1+Q2+Q3, and so on


    I am trying to produce a pivot table, summing the Sales for each quarter of each year, where I can take a look at:
    1. How much each quarter represents from each year, in percentages (full year would be 100%)
    2. Calculate a quarterly index, where the quarter with the lowest Total Sales would be 1, and the other quarters would be proportionally higher
    3. Calculate an index where the sum of indexes would be 10, and each quarter would have an index proportional to its Total Sales. Basically a simplified version of (1).
    4. Same as (3), but where I would set the sum-of-indexes to a user-defined value (say "12")

    The data is pretty large (about 900.000 records so far, and still growing), so I would like to avoid using formulae.
    Is this even possible without employing any add-ons...?
    I also have Python and pandas sinatlled, although I am a total noob with those, and this may be off-topic here

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,653

    Re: Pivot table with custom indexes

    Hi Horace,

    See the attached where when you drag change the column to Running Total it asks "of what other column". You give year and not quarter.

    Running Total of Quarter per Year.xlsx
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Registered User
    Join Date
    07-03-2013
    Location
    Timisoara, Romania
    MS-Off Ver
    2016
    Posts
    4

    Re: Pivot table with custom indexes

    Thank you for now, but this is not quite what I need. I do not have the individual sales data (your column "Sales") - I only have data from column ("sales_new") - see attached file.
    What I am trying to produce with a pivot are columns K thru N. Column J is an intermediary step, I do not need it per se, but I think I need it in order to calculate the other columns.

    Edit: I feel this is not possible to do without formulae, this is why I am inquiring if pivots can be used...
    Attached Files Attached Files
    Last edited by horace_vr; 12-31-2017 at 02:15 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,653

    Re: Pivot table with custom indexes

    OK Horace,

    First thanks for the rep - that always leads me back here and read the question again.

    I've pulled the Sales number over to the pivot and done other percent columns. See if one of them is what you want.

    Running Total of Quarter per Year PerCent Diffs.xlsx

    Then if you don't see what you need read:

    https://chandoo.org/wp/2012/11/06/mo...s-pivot-table/

    Then, if the above doesn't do what you need, you can put columns to the right of the pivot and do non-pivot calculations in those cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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