Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Expression with if() and aggr()

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Expression with if() and aggr()

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,

...

)

5 Replies
Not applicable

Expression with if() and aggr()

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

avg(aggr(AggrExpr1,AggrDim))

also gives me only 1 result.

Not applicable

Expression with if() and aggr()

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.

MVP
MVP

Re: Expression with if() and aggr()

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,

...

)

Highlighted
Not applicable

Re: Expression with if() and aggr()

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!

Not applicable

Re: Expression with if() and aggr()

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?

Community Browser