topic Expression with if() and aggr() in QlikView

Expression with if() and aggr()

Thu, 15 Mar 2012 11:51:44 GMT

Dear all,

I have a very weird problem with a a chart expression. The simplified problem: I have a table with a dimension going from 1 to 10. My expression needs to be calculated depending on the dimension. So I write as expression (with DIM the dimension):

if (DIM=1, valueForDim1,

if (DIM=2, valueForDim2,

if (DIM=3, valueForDim3, [...]

Normally this doesn't give me any problems. However this time I need to use the advanced aggregation function (with AggrDim the aggregation dimension and AggrExpr the aggregation expression):

if (DIM=1, avg(aggr(AggrExpr1,AggrDim)),

if (DIM=2, avg(aggr(AggrExpr2,AggrDim)),

if (DIM=3, avg(aggr(AggrExpr3,AggrDim)), [...]

Now I only get a result for my first dimension! The other dimensions are not calculated! When I delete the if(DIM=1...) then I only get a result for the second dimension (and so on). The aggregations work like a charm when I use them outside the if() clauses.

Does anyone know how to fix this? Is this a bug? Thanks!

Expression with if() and aggr()

Thu, 15 Mar 2012 11:55:07 GMT

In addition: removing the if from the expression and only using:

avg(aggr(AggrExpr1,AggrDim))

also gives me only 1 result.

Expression with if() and aggr()

Thu, 15 Mar 2012 12:03:06 GMT

Ok it clearly is a problem with my datastructure: somehow the chart dimension conflicts with the aggregation dimension. Meaning that in any other situation the above works without a problem.

Re: Expression with if() and aggr()

swuehl — Thu, 15 Mar 2012 13:25:54 GMT

Your problem description let me assume that the issue is related to the implicite DISTINCT qualifier of the aggr() function, so you might want to try an explicite NODISTINCT.

Though this might result in showing some numbers for each dimension value, the actual resulting numbers might be wrong.

Without knowing your specific data model, dimensions, expressions and requirements, it hard to tell more for now.

edit:

Another suggestion: Use pick() function instead of multiple embedded if() statements, like

=pick(DIMENSION,

Expression1,

Expression2,

...

)

Re: Expression with if() and aggr()

Thu, 15 Mar 2012 13:35:38 GMT

That's right. I found that out just trying a couple of things without really knowing what I'm doing. Using the NODISTINCT that works very well. Although I have to ressearch if the results are indeed OK. But I have no idea what the difference is between the default and the NODISTINCT aggregations.

Thanks for the tip about the pick() function!

Re: Expression with if() and aggr()

Thu, 15 Mar 2012 13:44:09 GMT

A question about this:

If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values

may generate more than one return value, depending on underlying data structure.

(from the reference guide)

With 'combination of dimension values', do they mean the dimension of the chart on the one hand and the dimension of the aggr() function on the other?