Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Garfunkal
New Contributor

Using a hierarchy to drill, but not aggregate.

Hi

 

I have a bit of a strange requirement where I am trying to use the hierarchical navigation elements of Qlik, but ignore some of the aggregation when moving thru the hierarchy.  There is a chance I’m overthinking this, but I can’t get the functionality right.

So, I have an existing 4 hierarchy from a file (ie not using Parent / Child ids – just set out with repeating entries at higher levels.  At the lowest level I have a Cost Amount, so it aggregates up to the full total at the top level of the hierarchy.  My high level requirement has been to add a ‘Driver metric’ for each record in each level of the hierarchy which will then be used in conjunction with the Cost Amount.  The issue is that the drivers follow no ‘pattern’ – some are used multiple times at different levels, and the hierarchies have no natural link.  I also can’t print the Driver Value at the lowest level and allow it to aggregate along with the Cost Amount.

 

I created (for simplicity sake!) 3 tables:

 

  • The Cost Amount Hierarchy, Flattened with a Level Identifier. This means that the Cost Amount is correct for each item, but obviously no longer ‘aggregatable’, with an identifier for the DriverName
  • A table of DriversNames and Driver values from a different data source
  • A lookup up of Costs Groupings (from table 1) and Driver Values (from table 2) to join them together into one table, linked to a master calendar etc.

 

Whilst this all works fine, I feel like still want to be able to create a drillable hierarchy in my app, but I don’t think I can in the way I have done it.  Is there any way to use the Hierarchy in conjunction with this new table to be able to navigate around?  For example, see that there are values in a particular Level 2 Item, and be able to click into it to see all of the L3 items relevant to L2, but without aggregating the drivers associated?  Does this all make sense?

 

below is a mockup of the data

 

Raw Tables  Final Table 
Cost Hierarchy          
           
L1L2L3L4  LevelLevelDescCostDriver 
UKNorthNorth WestNW1  1UK8575000 
UKNorthNorth EastNE1  2North7153000 
UKNorthNorth EastNE2  2South1422000 
UKNorthNorth CentralNC3  3North West50150 
UKSouthSouth WestSW1  3North East5243000 
UKSouthSouth EastSE2  3North Central1413000 
UKSouthSouth CentralSC3  3South West112000 
      3South East64510 
Cost Amounts     3South Central672000 
      4NW1505000 
L4Cost Amount    4NE1103000 
NW150    4NE2514157 
NE110    4NC31413000 
NE2514    4SW111150 
NC3141    4SE264511 
SW111    4SC3675000 
SE264         
SC367         
           
           
Driver Metrics          
           
Driver NameDriver value         
UK Total5000         
North3000         
South2000         
West 150         
East510         
NE 157         
SE511         
           
Driver Lookup          
Cost levelDriverName         
UKUK Total         
NorthNorth         
SouthSouth         
North WestWest         
North EastNorth         
North CentralNorth         
South WestSouth         
South EastEast         
South CentralSouth         
NW1Uk Total         
NE1North         
NE2NE         
NC3North         
SW1West         
SE2SE         
SC3UK Total         
Labels (3)
1 Reply
Garfunkal
New Contributor

Re: Using a hierarchy to drill, but not aggregate.

Perhaps I am over complicating this!  

 

Put basically I am some metrics which attach at the lowest level of granularity of a hierarchy.  

I have another metric which is mapped to each entry at each level of the above hierarchy - and I want to be able to divide one metric by the other.  This metric is defined from a separate hierarchy.  Up to now I have flattened the first one out so i can map to it, but this then loses any of the qlik analytical benefits.  

Can anyone offer any suggestions? 

Cheers!