topic Re: Holding a summed value through a drilldown in QlikView

Holding a summed value through a drilldown

brian_booden — Thu, 21 Feb 2013 13:39:14 GMT

I have a situation where i am selecting a colour based on thresholds at the top level of a UK postcode hierarchy. The drilldown group looks like this:

Area -> District -> Sector -> Postcode

e.g. AB -> AB53 -> AB53 5 -> AB53 5ST

I want to hold the colour calculated at Area level, and always use that, no matter where i am in the drilldown. So let's say i'm at the bottom of the hierarchy and have the following results for the above postcode

Drilldown Level	Value of Sum (Issued) at Drilldown level
Area	10000
District	2000
Sector	500
Postcode	30

I always want to use 10000 (the result at Area level), no matter the level of the drilldown, as the basis to calulate the (same!) colour against some variableised thresholds. I have tried various combinations of the TOTAL operator in the sum, and also tinkered with Aggr(), but without much success.

The only way that i have managed to get this properly working is using $x in set analysis to go back x selections, but that's not going to work if the user does anything other than perform a straight drilldown on the chart without making any other selections in the application.

Can anyone suggest the best way to do this as i must be missing something...

Re: Holding a summed value through a drilldown

giakoum — Thu, 21 Feb 2013 13:46:57 GMT

something like the attached?

Re: Holding a summed value through a drilldown

Gysbert_Wassenaar — Thu, 21 Feb 2013 13:52:36 GMT

See attached example.

Re: Holding a summed value through a drilldown

brian_booden — Thu, 21 Feb 2013 13:52:47 GMT

Hi Ioannis,

That's fine if they are just set up as separate dimensions, but i need a hierarchical drilldown group, at which point your example does not work.

Re: Holding a summed value through a drilldown

giakoum — Thu, 21 Feb 2013 14:03:12 GMT

True if the group is a Drill Down. It only works with Cyclic Group.

So you need to add the code proposed by Gysbert to make it work.

Thank you.

Ioannis.

Re: Holding a summed value through a drilldown

brian_booden — Thu, 21 Feb 2013 14:17:38 GMT

Thanks Gysbert, but i still have an issue. When i apply your syntax, i do get the right total, but i also get lots of extra sectors listed that, as you can see, are outwith the possible values in my Sector field. In the chart, i need to see only the sectors that are possible values in the sector field. I can see why this is happening - by setting the fields to blank in the set analysis, it's bringing other field values into play.

I have managed to get this far and this is what is causing me the real issue. I need to get rid of the extra sectors.

Re: Holding a summed value through a drilldown

giakoum — Thu, 21 Feb 2013 14:25:35 GMT

Add the p (possible values) function :

sum({<District=, Sector=,Postcode={$(=p(Postcode))}>} TOTAL <Area> Value)

Also in District and Sector if needed. See attached.

Re: Holding a summed value through a drilldown

Gysbert_Wassenaar — Thu, 21 Feb 2013 14:43:37 GMT

See attached qvw.

Re: Holding a summed value through a drilldown

brian_booden — Thu, 21 Feb 2013 15:01:16 GMT

Gysbert, thank you so much. I applied your logic to Ioannis' file and then my application, and it worked! Amazing, thank you so much