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

    A hierarchy visualization challenge

    Jasper Ennik

      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.

       

      NodePathNode1Node2Node3Node4Node5PartCodeAllocated costs
      S01\P01S01P01---A0.5
      S02\P03\P02\P01S02P03P02P01-A0.25
      S02\P05\P04\P02\P01S02P05P04P02P01A0.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:

      Node1Node2PartAllocated costs
      P02P01A0.5(Sum of all costs incurred for P02: 2 * 0.25)

       

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

      Node1PartAllocated costs
      P01A1(Sum of all costs inc

       

      When S02 is selected, displayed table should look like:

      Node1Node2Node3Node4Node5PartAllocated Costs
      S02P03P02P01-A0.25
      S02P05P04P02P01A0.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\P01S02P03P02P01-A0.25
      S02\P05\P04\P02\P01S02P05P04P02P01A0.25

       

      Instead of the desired output:

      Node1Node2PartAllocated costs
      P02P01A0.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
          Jasper Ennik

          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?