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?



Best Answer

  • LipChean_Soh
    Answer ✓

    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.