1 Reply Latest reply: Aug 6, 2014 5:29 AM by Jasper Ennik

# A hierarchy visualization challenge

Hey all,

I've got a bit of a challenge I'm trying to tackle,

Suppose we have a data table with the following hierarchy structure, in which costs of production orders are distributed to the parent orders which 'consume' the output of child orders.

 NodePath Node1 Node2 Node3 Node4 Node5 PartCode Allocated costs S01\P01 S01 P01 - - - A 0.5 S02\P03\P02\P01 S02 P03 P02 P01 - A 0.25 S02\P05\P04\P02\P01 S02 P05 P04 P02 P01 A 0.25

(S for sales order, P for production order)

I'd like to do the following:

A user selects a random production or sales order for which he/she wants to see the structure of costs, regardless of to which other prod.order or sales.order the selected order is allocated (regardless of its parents).

When P02 is selected, the displayed table shoud look like:

 Node1 Node2 Part Allocated costs P02 P01 A 0.5 (Sum of all costs incurred for P02: 2 * 0.25)

When P01 is selected, the displayed table should look like:

 Node1 Part Allocated costs P01 A 1 (Sum of all costs inc

When S02 is selected, displayed table should look like:

 Node1 Node2 Node3 Node4 Node5 Part Allocated Costs S02 P03 P02 P01 - A 0.25 S02 P05 P04 P02 P01 A 0.25

The way I've currently figured out to sum costs for a specific production order is by activating a user filter:

e.g.: NodePath = *P02*

This will show all lines in which P02 is present, selecting *P02* will show:

 S02\P03\P02\P01 S02 P03 P02 P01 - A 0.25 S02\P05\P04\P02\P01 S02 P05 P04 P02 P01 A 0.25

 Node1 Node2 Part Allocated costs P02 P01 A 0.5

Can you guys figure out any method to transform data to the desired output, without having to reload from source data wich specific hierarchy filters?

This should be a method which preserves the child hierarchy of the selected order.

(If I drop this user request of preserving the child hierarchy I could just set NodePath=*P02*, and only use a PartCode dimension in the output table. But that'd mean analysis of cost structure of child orders to the selected order is impossible)

I hope any of you guys has an idea which could point me towards making this possible

• ###### Re: A hierarchy visualization challenge

I actually managed to solve this problem, the goal was to dynamically display a random hierarchy child as root parent in the displayed hierarchy table. Without having to reload from source data.

(See explanation above)

An expression dynamically determines which Node# field from the source table to display for each column and for each row in the output pivot table. Im using the pick() function for this, as it turned out dollar sign expansion is not re-evaluated for each dimension value.

As I think this is quite an interesting case, I attached the solution to this post for future reference.

This example shows how to dynamically rearrange hierarchy tables, displaying a user-defined child node as the new hierarchy root parent, without having to reload from source.

There's just one problem left, in order to make Qlikview recalculate the dimension values after changing to a new root node, you need to manually collapse and expand the dimensions in the pivot table.

Does anybody know if it's possible to have qlikview calculate this automatically using the OnChange and OnInput triggers on the inputbox?