A hierarchy visualization challenge

[#1]

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

Instead of the desired output:

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

[#2 · Accepted solution]

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?