Using a hierarchy to drill, but not aggregate.

MrAndrews — Sat, 16 Nov 2024 05:36:14 GMT

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

L1	L2	L3	L4	Level	LevelDesc	Cost	Driver
UK	North	North West	NW1	1	UK	857	5000
UK	North	North East	NE1	2	North	715	3000
UK	North	North East	NE2	2	South	142	2000
UK	North	North Central	NC3	3	North West	50	150
UK	South	South West	SW1	3	North East	524	3000
UK	South	South East	SE2	3	North Central	141	3000
UK	South	South Central	SC3	3	South West	11	2000
				3	South East	64	510
Cost Amounts				3	South Central	67	2000
				4	NW1	50	5000
L4	Cost Amount			4	NE1	10	3000
NW1	50			4	NE2	514	157
NE1	10			4	NC3	141	3000
NE2	514			4	SW1	11	150
NC3	141			4	SE2	64	511
SW1	11			4	SC3	67	5000
SE2	64
SC3	67


Driver Metrics

Driver Name	Driver value
UK Total	5000
North	3000
South	2000
West	150
East	510
NE	157
SE	511

Driver Lookup
Cost level	DriverName
UK	UK Total
North	North
South	South
North West	West
North East	North
North Central	North
South West	South
South East	East
South Central	South
NW1	Uk Total
NE1	North
NE2	NE
NC3	North
SW1	West
SE2	SE
SC3	UK Total

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

MrAndrews — Mon, 17 Jun 2019 12:23:18 GMT

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!

topic Re: Using a hierarchy to drill, but not aggregate. in App Development

Using a hierarchy to drill, but not aggregate.

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