Find the hierarchy level of a particular dimension in pivot table

[santhoshkasam09]

Hi All,

I have 7 dimensions in my pivot table of which month is a dimension. I want to know at what level the Month is placed so that i can write a logic based on that.

Is there any way to find the same. please let me know. Thank you.

Regards

Santhosh Kasam

[isingh30]

Can you share your data in excel sheet or sample application? Kindly mention input & output you need.

Thanks.

[santhoshkasam09]

Just to elaborate the requirement, if i have the dimensions like below:

Country, City, Year, Month, Quarter, Item number

Now I know that the level of Month dimension is 4. but when the user drags it to some other level, the logic has to change. So i want to know the level of the Month dimension after user drags it according to his requirement.

Let me know if you still have any doubts. I think it does not require any dummy data

[isingh30]

Please check this document -

How to use - Dimensionality()

Thanks.

[santhoshkasam09]

Hi Ishtdeep,

we can keep a condition based on the level using Dimensionality but cannot find the hierarchy level of a dimension with this.

Thanks anyways.

Regards

Santhosh Kasam

[santhoshkasam09 · Accepted solution]

I did not find any function to find the hierarchy level of the Dimension but a logic can be written in the below way:

if(GetObjectField(0) = 'MONTH',1,

if(GetObjectField(1) = 'MONTH',2,

if(GetObjectField(2) = 'MONTH',3,

if(GetObjectField(3) = 'MONTH',4,

if(GetObjectField(4) = 'MONTH',5,

if(GetObjectField(5) = 'MONTH',6,

if(GetObjectField(6) = 'MONTH',7,

if(GetObjectField(7) = 'MONTH',8,

if(GetObjectField(8) = 'MONTH',9)))))))))

But i feel this as a lengthy solution and affects the performance. Please suggest if there is any other work around to this.

Thank you.

Regards

Santhosh Kasam

[santhoshkasam09]

The Above can be achieved by using the Match function as below:

Match('MONTH', GetObjectField(0), GetObjectField(1), GetObjectField(2), GetObjectField(3), GetObjectField(4), GetObjectField(5), GetObjectField(6), GetObjectField(7), GetObjectField(8), GetObjectField(9)).

I think this is a better way than earlier. Thank you.

Regards

Santhosh Kasam