topic Re: Dynamic fields on Aggr in QlikView

Dynamic fields on Aggr

danielact — Fri, 15 Jun 2012 15:48:56 GMT

Is there a way to change the fields used on an Aggr function? I'd like to use it in a the dimension of a Pivot table and I'm having some trouble.

Let's say I have 4 fields I'm using as dimensions - Field1, Field2, etc.

So I'm using (just to simplify) aggr(Number, Field1, Field2, Field3, Field4) as a part of a dimension.

But now, let's say I want to collapse one of the dimensions in the Pivot - let's say Dimension 3, so now I'll only be showing 3 dimensions, not 4. Is there a way to change the aggr function to only aggregate by what's open?

Re: Dynamic fields on Aggr

CELAMBARASAN — Fri, 15 Jun 2012 19:32:19 GMT

Hi,

Are you using aggr in expression?

Celambarasan

Re: Dynamic fields on Aggr

CELAMBARASAN — Fri, 15 Jun 2012 19:40:07 GMT

Hi,

Try with this type of approach

If(IsNull(Only(Field2)), Aggr(Number,Field1),

If(IsNull(Only(Field3))), Aggr(Number, Field1, Field2),

If(IsNull(Only(Field4)), Aggr(Number,Field1, Field2, Field3), Aggr(Number, Field1, Field2, Field3, Field4))))

Hope it helps

Celambarasan

Re: Dynamic fields on Aggr

Fri, 15 Jun 2012 20:04:18 GMT

Have you tried using the Dimensionality() function?

This function returns the number of expanded dimensions in the pivot table.

You can use it to define If..else... statement in the Pivot table expression.

Re: Dynamic fields on Aggr

danielact — Mon, 18 Jun 2012 13:30:21 GMT

The dimensionality function looks like it might help, if there's a way to also get the field name for each level. Any idea if there's a function to return that value?

Re: Dynamic fields on Aggr

danielact — Thu, 21 Jun 2012 13:11:35 GMT

It turns out that the dimensionality function won't work for me, as I need to use this is the dimension, not the expression.

Any other ideas on how to do this?