Formula based on List top level

JAbhi

I have a module with one of the dimension as a list 'Prod Type', with items HW1, HW2 and this list has a top level 'All'.

In one of the line items in this module i want to apply a formula like below :-

If<top level of Prod Type> then <Task1> else <Task2>.

At first i thought of using parent(item(list)) as for top level this will return blank, but the module is having other hierarchical lists as well for dimensions due to which this formulation is not working for me.

Is there any work around for this?

pyrypeura

Hi @JAbhi you can use variation of this logic to accomplish what toy need. You can use IF level = 0 THEN logic else other logic in your formula then.

Calculating levels in a hierarchy for dashboard filtering

Mark_W_Shemaria

May 31, 2018

Summary

This article describes the technique to dynamically filter specific levels of a hierarchy on a dashboard, and provides a method to select and visualize hierarchies on a dashboard.

Details

This article explains how to configure the calculation of the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level on a dashboard.

In this example, we have an organized hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy, we want to calculate a module value that returns the associated level that is to be displayed on a dashboard.

Notes and platform context

• The technique addresses a specific limitation within dashboards where a composite hierarchy's list level cannot be selected if the list is synchronized to module objects on the dashboard.

• The technique uses a static module based on the levels of the composite structure used for filtering of the object on a dashboard.
• The technique is based on utilizing the Summary Method "Ratio" on line items corresponding to the list levels of the composite hierarchy to define the values of the filtering line items. Note that this method is not a formula calculation, but rather a use of the Summary Method Ratio on each line item applied to the composite hierarchy.

Example list

In this example, a four-level list composite hierarchy list is used. The hierarchy in this example has asymmetrical leaf items per parent:

Defining the level of each list

In order to calculate the level of each item in each of the lists L1 - L4, we need to create a module that calculates the associated level of each member by this technique:

1) Create as many line items as levels of hierarchy, plus one technical line item

2) Configure the settings in the blueprint of the line items of this filtering module, per this example and table:

Line Item	Formula	Applies to	Summary	Summary method Setting Ratio
Technical line item*	1	(empty)	Formula
Level or L4 (lowest level)	4	Org L4	Ratio*	L3 / Technical
L3	3	Org L3	Ratio	L2 / Technical
L2	2	Org L2	Ratio	L1 / Technical
L1	1	Org L1	Ratio	L1 / Technical

When applying these settings, the filtering module looks like this:

*Note the Technical line item Summary method is using Formula. Alternatively, The Minimum Summary Method can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank.

The filtering module with Summary method applied results:

Use the line item at the lowest level—Level (or L4) (LOWEST)—as the basis of filters or calculations.

Applying a filter on specific levels in case of synchronization

When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.

In the example, we apply a filter which matches any of the level 4 and 1:

The following filtered dashboard result is achieved by using the composite hierarchy as a page selector:

---

View Post

derektslim

Hello JAbhi

When you use an IF statement, the condition must return TRUE or FALSE.

Thus, you will need to create a checking condition.

IF

ITEM('Prod Type') = 'Prod Type'.'All'

THEN

<Task 1>

ELSE

<Task 2>

Then change the Summary method to Formula so it will apply at the All.

Hope this helps!

Derek Lim

JAbhi

Thanks @derektslim , its funny i was missing small detail of changing summary, but yes this works. thanks man :)

JAbhi

Thanks @pyrypeura - This article also works for me, and i believe it will be a good practice to follow this, but for more small and less complex structure the other answer by @derektslim fits well.

derektslim

It tends to happen, even to the best of us! 😂

Glad it works. Cheers!