# Pivot table with custom indexes

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

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

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

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