How to reference the last level in a hierarcy, even when closed?
I have a calculation made on the sales person in our salesperson hierarcy. That calculation then needs to be averaged on the above levels. As it does in the table below, because we have the hierarcy open to the last level.
When we close it, so the salesperson is no longer shown, the we can no longer reference the salesperson, and therefore we do not get the average of the sales area, but it just uses the same calculation on the subtotal as it does on the sales person, see below.
The calculation looks like this:
if count(c1, all(c), m1) > 0 then (sum(c1, all(c), m1) / count(c1, all(c), m1)) else sum(c1, 0, m1)
But when we close, the children of level 3 i.e. level 4, does not exist and the count is 0.
I tried using all(h,c) but it seems like they are not hidden, it seems that they are regarded as non-existent.
For the same reasons using level 4 also fails, besides the fact that it would count all in level 4 and not just the children. Ragged Leaf will again reference the lowest open level, which is level 3.
So is there any reference that disregards that it is closed when I reference level 4?
Comments
Hi Søren,
A 'closed' level is the same as if the data hasn't been requested from the database. And with no data in the client (from that level) you cannot do the calculation.
In this case, your only option is to keep the level open - and then use 'Visibility' options to hide the data on e.g. level 4. Then the data are still available and can be used in calculations.
Notice that hiding the entire lowest level - while it may be ok in a crosstab, it will probably not work for a diagram.
/Ole
Please sign in to leave a comment.