Building a BOM hierarchy

[#1]

Dear all,

I am trying to build a BOM hierarchy in QV. The source file can be seen in the picture on the left hand side (blue).

Based on column F "Complete", I'd like to calculate the completeness of the each node.

Example:

You can see in line 16 "Seat left", which is not complete (0%) and in line 17 the "Seat right", which is complete (100%). Resulting that line 15 "seating area" is 50% complete.

It is important to note, that the completeness of the nodes are calculated from the childs and not parents. Let me make another example: You can see, from line 7 to line 10 all are complete, however the parent node is indicated in the source file as not complete. But that should not be regarded in qlikview. If the childs are all 100%, the parent should be 100% as well. You can see the what I expect to be calculated in column H.

I have tried, using the Hierarchy function in qlikview, as well as some other scripts, but failed to come up, with a functioning solution.

Any help is really appreciated! Thanks!

[marcus_sommer]

Maybe this is helpful: Bill of Materials.

- Marcus

[#3]

Hi Marcus,

I actually have seen this post and implemented it in my solution.
However, this solution also counts the parent nodes, instead of just using the child nodes and then aggregating those numbers up to a parent node.

[marcus_sommer]

I haven't done this kind of task by myself yet but I could imagine that you need to include the level within your expression, something like: count({< Level = {4}>} Complete).

- Marcus

[#5]

Good idea! In theory, something like that might be the solution. So I basically need to identify, the level, which is to be aggregated to the parent node.

In my example above saying count({< Level = {4}>} Complete) will not work because, the level 6 and level 4 need to be aggregated to the parent node. It could be any level though.

[marcus_sommer]

Maybe you need to include a further matching which levels belong together.

- Marcus

[#7]

I see. Any suggestions, how that is done best?

[marcus_sommer]

Not really - I think you have already a child-parent-relationship maybe you need a further one which is adjusted to these calculation. Another idea (really just an idea) is to check if the The As-Of Table approach might be useful in this case.

- Marcus

[#9]

Dear all,

I now have tried several solutions, that are provided in the community.

From how I understood them, none of them is the exact solution to the challenge at hand.

Any other ideas, how this could be handled?

- Mert

[gerrycastellino]

Hello Mert:

I have designed a unique way of visualizing Hierarchy data at different levels, not sure if this addresses your issue,

have a look here:

https://community.qlik.com/message/823989?&_ga=1.80947223.1647173608.1486493414#823989

Gerry.