topic Use subtotal in an expression in QlikView

Use subtotal in an expression

Wed, 13 May 2009 22:17:43 GMT

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.

Use subtotal in an expression

Anonymous — Wed, 13 May 2009 23:00:53 GMT

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

Use subtotal in an expression

Wed, 13 May 2009 23:52:27 GMT

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.