Holding a summed value through a drilldown

[brian_booden]

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...

[giakoum]

something like the attached?

[Gysbert_Wassenaar]

See attached example.

[brian_booden]

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.

[giakoum]

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.

[brian_booden]

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.

[giakoum]

Add the p (possible values) function :

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

Also in District and Sector if needed. See attached.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[brian_booden]

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