Help Needed: Calculated Dimension over Calculated Dimension

[#1]

Hi Community,

I have below as one of my calculated dimensions, but what I would like to do further is to have it calculated over Dim_02 as well (please see attached). However, Dim_02 is another calculated dimension, so whatever I tried have error out.

Any help will be much appreciated.

=aggr(if(rank(pick(match(D_COST_MAX,'C1_MAX_IND','C2_MAX_IND','C3_MAX_IND','C4_MAX_IND')

,sum(C1_MAX_IND)

,sum(C2_MAX_IND)

,sum(C3_MAX_IND)

,sum(C4_MAX_IND)

),4,0)<=2,D_COST_MAX),Dim_01, D_COST_MAX)

Here is what I have tried (or something similar), but proven unsuccessful.

=aggr(if(rank(pick(match(D_COST_MAX,'C1_MAX_IND','C2_MAX_IND','C3_MAX_IND','C4_MAX_IND')

,sum(C1_MAX_IND)

,sum(C2_MAX_IND)

,sum(C3_MAX_IND)

,sum(C4_MAX_IND)

),4,0)<=2,D_COST_MAX),Dim_01, Dim_02, D_COST_MAX)

[#2]

Hi,

Could you please let us know what could be the output you are expecting?

[#3]

Hi,

I would like to Top 2 Dimension to display top 2 labels whose members are C1_MAX_IND, C2_MAX_IND, ..., C4_MAX_IND based on Dim_01 and Dim_02. Essentially, I am counting the indicators that determine which metrics (i.e. C1, C2, C3 and C4) are maximum when compare on a row level. For example, if C1_MAX_IND = 1, then it means that C1 is maximum on that particular row.

My problem is, as an example, when I select Dim_01 = D_03 and Dim_02 = ">", we will see that Top 2 are C2_MAX_IND and C3_MAX_IND which is what I want. You will notice that there are 4 of C2_MAX_IND and 2 of C3_MAX_IND. However, if I deselect Dim_02, then the Top 2 dimension does not display desired information.

I think the root of my problem is my Top 2 calculation is based on Dim_01 only, and I cannot seem to figure out how to include Dim_02 into my Top 2 calculation.

I am sure there is a better way of doing this...

[#4]

Please refer to the attached file.

Instead of trying to go down the "calculated dim over calculated dim" path, I used the calculated expression to show only the top 2 records. I also created an inline table to contain the "<= and >" operators in order to use it within Pivot Table.

[#5]

Thank you.

A quick question though, I don't quite understand why Dim_01 does not display all of its member?

[#6 · Accepted solution]

That is because I left Dim_01 out of the aggregation. I've attached another version for you. Is this what you're expecting to see?

[#7]

Great, thank you. This is what I am looking for.

[#8]

Hi Shawn084,

just a quick follow-up question. If I have a long list of variables (i.e. C1_MAX_IND,..., C100_MAX_IND, ....), do you have any suggestion on how to automate this? Just wondering if I can avoid typing very long expression.

Thank you.

[#9]

Personally, I would transform the crosstable (from your POC spreadsheet) into a straight table.

That way you would have a new column called (for example) C_MAX_ID. And the values in this column would contain C1_MAX_IND, C2_MAX_IND, C3_MAX_IND, etc...

At such point, you could edit the set analysis expression to include something like D_COST_MAX = C_MAX_IND.

This way you wouldn't have to write long expressions to accommodate for every possible Cn_MAX_ID columns.

Hope that gives you some idea.