• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a way to have a Table Hierarchy? Or Multiple Header Tables?

M

Mark Bit

New Member
Anyone know if it is possible, and if so, how to have multiple headers in a table? I know it sounds counterintuitive but I would like a way to pivot a bulk set of data (set in an Excel Data Table) but have the option to choose 1 of 2 Options when choosing my data header for the Pivot.

Let me try and explain a scenario. So each row has an Account, Part #, a specific week ending date units sold, and a specific week ending date unit dollars. For each account, we sell maybe 5-7 products. So Retailer A has 5 unique rows, Retailer B has 7 unique rows, yadda yadda.

I have 4 tabs, very similar of one another, the only variable is the year (specified by the tab) – or – table name … and the removal and inclusion of obsolete and new products.

Our Sales weeks are Sunday-Saturday. So, I have 2 column headers that read 11/15 Units and 11/15 Dollars; for this past week.

However, If I wanted to compare this past week to last year or 2012, using Power Pivots, I would not be able to – since “week endings” do not lineup. That is to say in 2013 Week 46 (this past week-end week) correlates to 11/16.

So I would not be able to do a 1:1 comparison easily within a Pivot due to the variation in dates. So logically, I would need some sort of common denominator. I would imagine this would be Week “X.” So essentially two separate (or hierarchy?) column headers. I would either need “Week 46″ act as a parent – above both “11/15 Units” and “11/15 Dollars” … or alternatively two separate “co-headers” right above them: “Week 46 Units” and “Week 46 Dollars”

This way, I could compare multiple Week Ending sales data, across multiple tables, using a Power Pivots, based on a common property.

Does anything like this exist? I feel like I would have come across it by now if it does.

And to note, I cannot simply just change the way we handle data to reflect a Week # and omit the XX/XX date methodology. I wish I could, cause that would be abundantly easier.


....Example Included.
 

Attachments

Hi Mark,

As your last paragraph seems to indicate, good data table setup is key. But, sometimes we have to work with what we're given. :)

I don't believe we can do hierachy's like you show w/o basically changing the complete layout (in which case, we would just switch naming).

So, my proposal would be to use INDEX. Since the week 1 units are always the 5th column, we could refer to it via:
=INDEX(RRR_201535[#Data],,5)

Obviously, the last argument would be supplied via another function or a cell reference. So, I can imagine having a cell where you fill in a week number (say 2 in cell A2). A quick formula of:
=4+(a2-1)*2+1
will calculate the correct column number within table for Units. Formula for dollars would be:
=5+(a2-1)*2+1

Does that give you some ideas?
 
I would have to see an example I suppose, but I at least know I need to do some work-around haha. It just would be nice to easily compare Week 1 to Week 1 of any given year - via a Power Pivot. Ideally I would bypass the date structure - and simply use Week 1, Week 2, etc - for universal purposes. However, the data entry is somewhat contingent on the dates.
 
Here's an example...you can change the value in A1 to be a number indicating which week you want, and formulas pull values for that week.
 

Attachments

Hmmm interesting, I think I certainly could run with this. Multi week will take some thought.

Thanks again for all your help. Much appreciated! :)
 
You must log in or register to reply here.
Back
Top