2 of 2 people found this helpful
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
ELSEIF ISNULL(ATTR([Product Name]) = "*") THEN //2nd level of hierarchy
ELSE //3rd level of hierarchy
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
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!
Good stuff, thanks Jonathan. I'm still playing around with it but I can see where this should work for what I need.
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?
1 of 1 people found this helpful
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.
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?
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 > 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?
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.
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.
Thanks for this. I will try to see if I can convince the client to use calculated fields in place of herarchies.
This doesn't work anymore does it?