12 Replies Latest reply on Jul 25, 2018 7:23 AM by Robertino Bonora

# Determine which level of a hierarchy is active (expanded)

Is there a way to determine which level of a hierarchy is currently active (expanded)?

I have a 4-level hierarchy in my worksheet: The user can expand or collapse this hierarchy as necessary, so at any point in time the lowest level of the hierarchy that is active could be hierlevelA, hierlevelB, hierlevelC, or hierlevelD. I would like to create a calculated field off of this info, basically something like this:

IF [lowest level of hierarchy = "hierlevelA"] THEN

200

ELSEIF [lowest level of hierarchy = "hierlevelB"] THEN

MAX(Sales)

ELSIF [lowest level of hierarchy = "hierlevelC"] THEN

MIN(Volume)

ELSE

5000

END

Just curious if this was possible. Thanks.

JPL

• ###### 1. Re: Determine which level of a hierarchy is active (expanded)

Hi John,

Here's a test that works using the Superstore Sales products hierarchy (Category, Sub-Category, Product Name). You start with top level of the hierarchy, and test if the ATTR() function is returning Null or not - ATTR() returns Null when there are multiple values, but a special version of Null that doesn't work for the regular ISNULL() function.

IF ISNULL(ATTR([Sub-Category]) = "*") THEN // top level of hierarchy

MAX([Sales])

ELSEIF ISNULL(ATTR([Product Name]) = "*") THEN //2nd level of hierarchy

MIN([Volume])

ELSE //3rd level of hierarchy

5000

END

The test will fail in two situations that I know of:

- If you have Null values within the hierarchy.

- If any of the children of the hierarchy are already in the view, like for some table calculation situations where you might put a field on the Level of Detail Shelf.

To make the test work for hierarchies that aren't strings (like dates), then wrap the ATTR() in a STR() to force it to a string comparison.

Thanks to Joe Mako for the ATTR() test: http://community.tableau.com/thread/108930

Jonathan

4 of 4 people found this helpful
• ###### 2. Re: Determine which level of a hierarchy is active (expanded)

Hi John,

One way to do this may be to use a parameter. However, when using a parameter, you most likely would not want to keep your hierarchy. Instead, the parameter would name each of these levels of the hierarchy. Hope that makes sense!

-Tracy

• ###### 3. Re: Determine which level of a hierarchy is active (expanded)

Good stuff, thanks Jonathan. I'm still playing around with it but I can see where this should work for what I need.

• ###### 4. Re: Determine which level of a hierarchy is active (expanded)

Hi Jonathan,

There is a third scenario where the solution would not work. If a level contains just one member, then the parent level will show incorrect data (i.e. the lower level's data).

In your example, if the [Product Name] level has just one member for a particular [Sub Category] then the [Sub Category] level will show 5000 instead of MIN(Volume).

Do you know if there is any work around for this?

• ###### 5. Re: Determine which level of a hierarchy is active (expanded)

Hi Viswanath,

There is a workaround for this, however it comes with its own problems. Basically, when a given level contains just one child, there's no way in Tableau to identify what level you are at, so it always returns the level of the child. The workaround is to then use a table calculation to find out the lowest hiearchy level in the view, and return that, but that calc will fail if all the members of a certain level in the hierarchy each only have one child. See the attached workbook for details.

Therefore, using a parameter as Tracy suggested is probably your best solution. There's a nice article on this from Clearly and Simply at http://www.clearlyandsimply.com/clearly_and_simply/2012/03/dynamic-hierarchies-with-tableau.html.

Jonathan

2 of 2 people found this helpful
• ###### 6. Re: Determine which level of a hierarchy is active (expanded)

Thanks Jonathan.

As mentioned in my other post, it looks like WINDOW_MIN() would work for me for now. But if I do end up having just one child for all the members of a certain level you have suggested use of a parameter is the best solution. I am not clear with this.

Are you suggesting we create a parameter with the level names and use that to determine the level?

Thanks

Vishy

• ###### 7. Re: Determine which level of a hierarchy is active (expanded)

There are a variety of options depending on the complexity of your data and what you want to do. Here's what I was thinking, based on the Clearly and Simply post I'd referenced and the Superstore Sales data:

Set up a parameter have values like:

Category

Category > Sub-Category

Category > Sub-Category > Product Name

Set up the CASE statements for the hierarchy dimensions as in the post.

Set up an additional CASE statement that returns different measures or values based on the parameter, and use that for the result.

Does that make sense?

Jonathan

1 of 1 people found this helpful
• ###### 8. Re: Determine which level of a hierarchy is active (expanded)

Hi Jonathan,

Thanks a lot for your assistance.

Ok. Here is my requirement.

Lets say I have a hierarchy with 3 levels

Category->Sub Category-> Product Name

When the user looks at the data at the Category level he should see Measure A. When he drills down to Sub Category then he should see Measure B and when he drills down to Product he should see Measure C.

If we use parameters, we are expecting the users to make selections in the parameters as opposed to actually using the hierarchy to drill down.

Is there a way to accomplish my requirement? BTW, just got to know that Window_Min() might not meet my requirement since it is a table calc and I cannot use table calcs.

Thanks

Vishy

• ###### 9. Re: Determine which level of a hierarchy is active (expanded)

Hi Viswanath,

There's no way I know of to do exactly what you want just using the built-in drill down through the hierarchy. Either you can use the calculations I outlined above (with their restrictions, such as needing a table calculation) to test for the hierarchy level, or you can use parameters and calculated fields, and effectively turn off Tableau's built-in drilldown by using some calculated fields in place of the hierarchy dimensions.

Jonathan

• ###### 10. Re: Determine which level of a hierarchy is active (expanded)

Hi Jonathan,

Thanks for this. I will try to see if I can convince the client to use calculated fields in place of herarchies.

Vishy

• ###### 11. Re: Determine which level of a hierarchy is active (expanded)

This doesn't work anymore does it?

• ###### 12. Re: Determine which level of a hierarchy is active (expanded)

Hi! in my actual project i have the same issue, i have a hierarchy with two levels Category --> Errors. I have a table where i put Year/Month, Category, Errors in the rows wich can be expanded or not expanded in any time and in the columns i put SUM([Sales]).

The client its actually ask my to highlight the MAX value of each posibility, for example:

if i have the table with category being the top level of the hierarchy i have to highlight de max values In the other hand, if i have the next table i have to hightlight the max values but with the hierarchy expanded,

Any idea on how to do this? i tried with a LOD but cannot succed.

Thanks

Robertino