Pivot Table data pulling fields from data model in a PITA way

[ShadezExcel]

Crosspost with the basic Excel forum:

I am basically looking for a way to collapse 6a and 6b into one line so that all of the calculations happen on one line. I am x-posting because I suspect that the data model is causing some rigidity.

http://www.mrexcel.com/forum/excel-questions/843740-pivot-table-how-limit-data-one-row.html

 

[ImkeF]

You should use aliases for cases like this (there you would give the 6a and 6b the same name: newest one. Create a hierarchy with the real names - then they can be doublecklicked if so wants to see whats behind).

Fill in an alias column in all of your dimension tables for the names that could change and use these in your reports. So if someone decides that sth needs to be changed, you don't need to adopt your reports.

This is specially important with dimensions, where names are used for filtering or other structures in your reports (i.e. account names): You don't want to adjust your reports, if someone decides to change the names (as they are seldom used as keys, this can happen quite often!)

In my eyes, you can simply not have enough aliases
Imke

 

[ShadezExcel]

Good morning!

Thanks for the recommendation. By "create a hierarchy", I am guessing that you mean to make a separate table. How do I make this table future proof so that if a new customerID is made, it is automatically added?

 

[ImkeF]

Well, it depends on how your data is currently structured:

Where does the customer name that shall be shown for no6 currently sit?
a) In the table that also contains 6a and 6b (so they will be the key-level)? Or
b) in a table where 6 is the key-level (only 1 line per customer name)

If it's b) and you have a table that has only one (the current?) name per customer, then you're at what Scott has suggested in the first post (might this be your "current customer card" you've mentioned?): Link that table as a lookup table to your table where the 6a and 6b items are and create a calculated column (using RELATED) to drag that name there. Then you have the 2 columns you need in 1 table to create a hierarchy (as far as I'm aware, hierarchies can only be created within 1 table).

If it's a) then the challenge might be to allocate the most current name to all the older ones for each customer. A calc column using EARLIER might work for that.