Use subtotal in an expression

[#1]

Hi Folks

I have a pivot table which has 4 hierarchial levels and a single value.

I would like at level 1 to change the colour of the text if the sum of all values for that level is N, and of course I'd like to do the same for level 2 & 3, whereas level 4 is the lowest level and so simply doing IF(SUM(X)<>0,RED()) works perfectly.

Anybody know how I can use the value of the partial sum in an expression?

Cheers,

Nigel.

[Anonymous]

Hi Nigel,

I think you should be able to use aggr() here, for example on level 3:

IF(aggr(sum(X), Dim1, Dim2, Dim3)<>0,RED())

If you aggregate the sum over the current dimension level you're on it should color the pivot dimension accordingly

[#3]

Hi Jsn

Thanks for the very prompt response, much appreciated.

I tried that and it works fine for levels 2 and 3, but doesn't work on level 1. If I put the statement in as anexpression (i.e. aggr(sum(x),Dim1) then what I get is a blank value at the first line and a value of 4 on the second line. It would appear that the expression to change colour is probably referencing the first line.

My question is though, why would the result of this expression by any different for any row within the same group? That seems a little bit weird to me?

Cheers,

Nigel.