topic Re: Using a hierarchy to drill, but not aggregate. in App Development
https://community.qlik.com/t5/App-Development/Using-a-hierarchy-to-drill-but-not-aggregate/m-p/1592558#M43561
<P>Perhaps I am over complicating this! </P><P> </P><P>Put basically I am some metrics which attach at the lowest level of granularity of a hierarchy. </P><P>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. </P><P>Can anyone offer any suggestions? </P><P>Cheers!</P>Mon, 17 Jun 2019 12:23:18 GMTGarfunkal2019-06-17T12:23:18ZUsing a hierarchy to drill, but not aggregate.
https://community.qlik.com/t5/App-Development/Using-a-hierarchy-to-drill-but-not-aggregate/m-p/1591433#M43442
<P>Hi</P><P> </P><P>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.</P><P>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.</P><P> </P><P>I created (for simplicity sake!) 3 tables:</P><P> </P><UL><LI>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</LI><LI>A table of DriversNames and Driver values from a different data source</LI><LI>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.</LI></UL><P> </P><P>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?</P><P> </P><P>below is a mockup of the data</P><P> </P><TABLE border="1"><TBODY><TR><TD><STRONG>Raw Tables</STRONG></TD><TD> </TD><TD> </TD><TD><STRONG>Final Table</STRONG></TD><TD> </TD></TR><TR><TD>Cost Hierarchy</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>L1</STRONG></TD><TD><STRONG>L2</STRONG></TD><TD><STRONG>L3</STRONG></TD><TD><STRONG>L4</STRONG></TD><TD><STRONG> </STRONG></TD><TD><STRONG> </STRONG></TD><TD><STRONG>Level</STRONG></TD><TD><STRONG>LevelDesc</STRONG></TD><TD><STRONG>Cost</STRONG></TD><TD><STRONG>Driver</STRONG></TD><TD> </TD></TR><TR><TD>UK</TD><TD>North</TD><TD>North West</TD><TD>NW1</TD><TD> </TD><TD> </TD><TD>1</TD><TD>UK</TD><TD>857</TD><TD>5000</TD><TD> </TD></TR><TR><TD>UK</TD><TD>North</TD><TD>North East</TD><TD>NE1</TD><TD> </TD><TD> </TD><TD>2</TD><TD>North</TD><TD>715</TD><TD>3000</TD><TD> </TD></TR><TR><TD>UK</TD><TD>North</TD><TD>North East</TD><TD>NE2</TD><TD> </TD><TD> </TD><TD>2</TD><TD>South</TD><TD>142</TD><TD>2000</TD><TD> </TD></TR><TR><TD>UK</TD><TD>North</TD><TD>North Central</TD><TD>NC3</TD><TD> </TD><TD> </TD><TD>3</TD><TD>North West</TD><TD>50</TD><TD>150</TD><TD> </TD></TR><TR><TD>UK</TD><TD>South</TD><TD>South West</TD><TD>SW1</TD><TD> </TD><TD> </TD><TD>3</TD><TD>North East</TD><TD>524</TD><TD>3000</TD><TD> </TD></TR><TR><TD>UK</TD><TD>South</TD><TD>South East</TD><TD>SE2</TD><TD> </TD><TD> </TD><TD>3</TD><TD>North Central</TD><TD>141</TD><TD>3000</TD><TD> </TD></TR><TR><TD>UK</TD><TD>South</TD><TD>South Central</TD><TD>SC3</TD><TD> </TD><TD> </TD><TD>3</TD><TD>South West</TD><TD>11</TD><TD>2000</TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>3</TD><TD>South East</TD><TD>64</TD><TD>510</TD><TD> </TD></TR><TR><TD><STRONG>Cost Amounts</STRONG></TD><TD><STRONG> </STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD>3</TD><TD>South Central</TD><TD>67</TD><TD>2000</TD><TD> </TD></TR><TR><TD><STRONG> </STRONG></TD><TD><STRONG> </STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>NW1</TD><TD>50</TD><TD>5000</TD><TD> </TD></TR><TR><TD><STRONG>L4</STRONG></TD><TD><STRONG>Cost Amount</STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>NE1</TD><TD>10</TD><TD>3000</TD><TD> </TD></TR><TR><TD>NW1</TD><TD>50</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>NE2</TD><TD>514</TD><TD>157</TD><TD> </TD></TR><TR><TD>NE1</TD><TD>10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>NC3</TD><TD>141</TD><TD>3000</TD><TD> </TD></TR><TR><TD>NE2</TD><TD>514</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>SW1</TD><TD>11</TD><TD>150</TD><TD> </TD></TR><TR><TD>NC3</TD><TD>141</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>SE2</TD><TD>64</TD><TD>511</TD><TD> </TD></TR><TR><TD>SW1</TD><TD>11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>4</TD><TD>SC3</TD><TD>67</TD><TD>5000</TD><TD> </TD></TR><TR><TD>SE2</TD><TD>64</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SC3</TD><TD>67</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Driver Metrics</STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG> </STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Driver Name</STRONG></TD><TD><STRONG>Driver value</STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>UK Total</TD><TD>5000</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>North</TD><TD>3000</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>South</TD><TD>2000</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>West </TD><TD>150</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>East</TD><TD>510</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>NE </TD><TD>157</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SE</TD><TD>511</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Driver Lookup</STRONG></TD><TD><STRONG> </STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Cost level</STRONG></TD><TD><STRONG>DriverName</STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>UK</TD><TD>UK Total</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>North</TD><TD>North</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>South</TD><TD>South</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>North West</TD><TD>West</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>North East</TD><TD>North</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>North Central</TD><TD>North</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>South West</TD><TD>South</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>South East</TD><TD>East</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>South Central</TD><TD>South</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>NW1</TD><TD>Uk Total</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>NE1</TD><TD>North</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>NE2</TD><TD>NE</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>NC3</TD><TD>North</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SW1</TD><TD>West</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SE2</TD><TD>SE</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SC3</TD><TD>UK Total</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>Thu, 13 Jun 2019 08:37:05 GMThttps://community.qlik.com/t5/App-Development/Using-a-hierarchy-to-drill-but-not-aggregate/m-p/1591433#M43442Garfunkal2019-06-13T08:37:05ZRe: Using a hierarchy to drill, but not aggregate.
https://community.qlik.com/t5/App-Development/Using-a-hierarchy-to-drill-but-not-aggregate/m-p/1592558#M43561
<P>Perhaps I am over complicating this! </P><P> </P><P>Put basically I am some metrics which attach at the lowest level of granularity of a hierarchy. </P><P>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. </P><P>Can anyone offer any suggestions? </P><P>Cheers!</P>Mon, 17 Jun 2019 12:23:18 GMThttps://community.qlik.com/t5/App-Development/Using-a-hierarchy-to-drill-but-not-aggregate/m-p/1592558#M43561Garfunkal2019-06-17T12:23:18Z