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
Bookmarks