Calculating Levels in Hierarchy / Filter out sub-level

[MarkTurkenburg]

Hi team,

We have a 5 level composite list & in the top level (Region L1) there is a sub-composite list (by that I mean there are list items with parents within the same list, i'm not sure of the technical name for this - see attached screenshot where China, Korea & Japan are child parent of Asia, all within Region L1)

I have created a module that calculates the hierarchy level for filtering purposes (screenshot 2) on a dashboard but I wish to filter out the China, Korea & Japan list items in Region L1. Currently all items in Region L1 have 1 as the value

Is there a way to achieve this?

 

[LipChean_Soh]

Hi @MarkTurkenburg 

When you say 'filter out', do you mean you just want to show 'Elimination, Europe, Australia & New Zealand, Americas, Emeis holding, Asia'?

And i assume the module is applying a list that is at least L2, i.e. could be L3, L4, L5?

Thanks,

LipChean

[MarkTurkenburg]

Hi @LipChean_Soh 

Yes I mean I wish to display only 'Elimination, Europe, Australia & New Zealand, Americas, Emeis holding, Asia'. Without this sub-list within R1 I would just reference 'Region Hierarchy Definition' module on the appropriate Line Item (L3 in this case as my target module is dimensionalised by L3 - see attached screenshot) but this will display 'China & South East Asia' etc

Thanks

[LipChean_Soh]

Hi @MarkTurkenburg 

Pls see if the following works.

1. Ratio for L2 = L1/ Technical

2. Ratio for L3 = L2/ Technical

Then in any module which uses L3, you can do the filtering as shown above.

Thanks,

LipChean

[MarkTurkenburg]

Hi @LipChean_Soh 

I already had what you suggested as my original solution but unfortunately the 'China & SE Asia' item is apart of L1 list & hence will return 1 (please see attached screen shots)

Thanks

 

[LipChean_Soh]

Hi @MarkTurkenburg 

Can you share screen shots of your L1 and L2's ratio?

Thanks,

LipChean

[MarkTurkenburg]

Hi @LipChean_Soh 

Please see attached screenshots

Thanks

[LipChean_Soh · Accepted solution]

Hi @MarkTurkenburg 

I apologize for overlooking something you said, i.e. Japan, Korea, China & SEA are also part of L1 Region.

That makes L1 a ragged hierarchy. In many sense, 'Japan, Korea, China & SEA' are similar to 'Elimination, Europe, Australia & NZ, Americas, Emeis holding' while Asia is the odd one out.

I can only think of a method that combines some dynamic + manual steps, i.e.

1. Use filter to select L3=1

2. Manually select 'Elimination, Europe, Australia & NZ, Americas, Emeis holding, China & SEA' and click 'Show'. Using 'Show' instead of 'Hide' will allow you to keep adding new Asian countries, but they won't be displayed in the module.

Perhaps there's a smarter way of doing this, let's see what the other forum members say.

Thanks,

LipChean

[MarkTurkenburg]

Thanks @LipChean_Soh