A usable hierarchy in Tableau would be a 'balanced' one:
-- only 'leaf' rows are present in the datasource;
-- every level has it's own column (of non-Null values);
-- when a hierarchy is ragged, the (non-Null) values
are propagated from the higher levels to the lower ones.
Please find in the attached an example of such hierarchy.
Hope it could help a bit.
Test Hierarchy_v10.2_YF.twbx 76.7 KB
Many thanks for your help. To make sure I understand you correctly: in order to follow your 3-bullits solution you have created 5 calculated fields. These fields fill out NULL fields with the value of their direct parent level. Then you use the standard hierarchy function of Tableau to combine these 5 calculated fields into a hierarchy. If that is it: very simple and I like it. Really wondering why I read so many difficult solutions for this problem then...
One thing remains: As soon as the object reaches it lowest level (but not the lowest hierarchy level of the entire dataset, the value now gets repeated on the subsequent levels. Is there a solution to that?
1 of 1 people found this helpful
I like you like it :-)
And you get the logic just right.
The fine point is the join itself --
it's the 'opposite' of your original one
(the join columns are reverted all the way).
So the hierarchy is built from the root down.
That's why the Context (aka Datasource) Filter is applied.
And for your remaining question, how would you like to see this?
Which value should be written for the Planten at the Levels 4 and 5?
In addition to my reply: I added some data which I want to see, mapped on the object tree we (well.. you) created. This starts to mess things up as you see in the attached workbook. In the workbook I entered some calculated fields which check with T/F whether the object name reached its deepest level, but I don't use these fields yet.
(Some use case background: I track the testresults whether requirements (linked to 1 or more object IDs) are tested succesfully. I store that in a system which can give me the results in tabular form.)
Test Hierarchy_v10.2_SN.twbx 78.6 KB
Sander, could you please mockup the desired result (in Excel)?
I couldn't quite get the requirements (yet).
The result can look like this. Please note an object can have results connected directly to the object and have results from child objects.
Own results Own results + underlying objects Object ID Level 1 Level 2 Level 3 Level 4 Level 5 OK NOK OK NOK 6 Grond 1 0 13 3 1 Huis 4 1 11 3 2 Zolder 0 0 5 2 7 Bed 2 0 5 2 8 Dekens 0 1 0 1 9 Kussens 3 1 3 1 3 1e verdieping 1 0 1 0 4 2e verdieping 1 0 1 0 5 Tuin 1 0 1 0 10 Planten 0 0 0 0 11 Tuintafel 0 0 0 0 Sum / Check 13 3 TRUE TRUE
The excel file, including data and the desired output table is attached.
Hierarchy test.xlsx 44.0 KB