Drill-Down and Drill-Up without SPLIT and FINDNTH
Aug 8, 2020Klaus Schulte
Drill-Down and Drill-Up without SPLIT and FINDNTH. — This post is an addition to previous posts on this blog and on Rosario Gauna’s blog.
In the meantime it turned out, that not all of the functions used in Rosario’s initial blog are supported for every available data connection in Tableau. I personally came across an issue with SAP HANA live connections, where SPLIT and FINDNTH are not supported.
This post will adjust the formulas for these use cases.
This step is unchanged to the original post. Create two parameters “Level” and “Path”.
|PARAMETER||TYPE||CURRENT VALUE||ALLOWABLE VALUES|
Drawing on Rosario’s example, we take Superstore to create a hierarchy of four levels, with a fifth level to return to the first level.
Therefore we need:
|DD Level||IIF([Level] = 4, 0, [Level] + 1)|
|DD Label||CASE [Level]|
WHEN 0 THEN [Region]
WHEN 1 THEN [State]
WHEN 2 THEN [City]
WHEN 3 THEN str([Postal Code])
WHEN 4 THEN “Top”
Convert the “DD Level” field to a dimension with a right click or just drag it to dimensions.
DD Path is the dimension, that will feed the Path parameter. I will add the current “Level” to the delimiter. I also recommend to use a uncommon string as delimiter to not run into problems at a later stage.
|DD Path||IIF([Level] = 4, “Top”, [Path] + “|”+str([Level]) + [DD Label])|
According to the addition of the Level in die DD Path field, we have to add this information to the DD Filter field.
|DD Filter||CONTAINS(“Top|0” + [Region] + “|1” + [State] + “|2” + [City] + “|3” + STR([Postal Code]), [Path])|
|DU Level||INT([Order Date]) % 5|
Convert “DU Level” to a dimension with a right click or just drag it to dimensions.
The formula described here calculates the residual of dividing the numerical value of the date of the “Order Date” field by 5, giving possible values as numbers between 0 and 4. That is, we are arbitrarily grouping the days into five groups (0, 1, 2, 3, 4), one for each required value for the new dimension or menu options required.
This technique is known as Internal Data Densification. Make sure to check, whether the selected field from the data (here based on Order Date) gives back a valid new dimension with the required number of members.
|DU Path||IIF([DU Level]=[Level], [Path], LEFT([Path],FIND([Path],”|”+STR([DU Level]))-1))|
DU Path takes the relevant part from the Path parameter using LEFT and FIND functions and the combination of delimiter and pervious DU Level.
|DU Label||IIF([DU Level] = 0, “TOP”, RIGHT([DU Path],LEN([DU Path])-FIND([DU Path],”|”+STR([DU Level]-1))-1))|
DU Label also takes the relevant part from the Path parameter using RIGHT, LEN and FIND functions and the combination of delimiter and pervious DU Level.
|DU Filter||[DU Level] <= [Level]|
|DU Color||[DU Level] = [Level]|
The steps to put together the views and to set the parameter actions are unchanged.
You can find them in Rosarios initial post or in this workbook on Tableau Public:
Thanks for reading — I hope you find good use cases to drill-down and drill-up without SPLIT and FINDNTH!