Calculating Levels in Hierarchy / Filter out sub-level

MarkTurkenburg
Contributor

Calculating Levels in Hierarchy / Filter out sub-level

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LipChean_Soh
Certified Master Anaplanner

Re: Calculating Levels in Hierarchy / Filter out sub-level

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

View solution in original post

8 REPLIES 8
LipChean_Soh
Certified Master Anaplanner

Re: Calculating Levels in Hierarchy / Filter out sub-level

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?

LipChean_Soh_0-1623632212053.png

 

Thanks,

LipChean

 

MarkTurkenburg
Contributor

Re: Calculating Levels in Hierarchy / Filter out sub-level

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
Certified Master Anaplanner

Re: Calculating Levels in Hierarchy / Filter out sub-level

Hi @MarkTurkenburg 

 

Pls see if the following works.

LipChean_Soh_1-1623634965593.png

 

LipChean_Soh_0-1623634943198.png

1. Ratio for L2 = L1/ Technical

2. Ratio for L3 = L2/ Technical

 

LipChean_Soh_2-1623635013170.png

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

 

Thanks,

LipChean

MarkTurkenburg
Contributor

Re: Calculating Levels in Hierarchy / Filter out sub-level

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
Certified Master Anaplanner

Re: Calculating Levels in Hierarchy / Filter out sub-level

Hi @MarkTurkenburg 

 

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

 

Thanks,

LipChean

MarkTurkenburg
Contributor

Re: Calculating Levels in Hierarchy / Filter out sub-level

Hi @LipChean_Soh 

 

Please see attached screenshots

 

Thanks

LipChean_Soh
Certified Master Anaplanner

Re: Calculating Levels in Hierarchy / Filter out sub-level

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

View solution in original post

MarkTurkenburg
Contributor

Re: Calculating Levels in Hierarchy / Filter out sub-level

Thanks @LipChean_Soh