Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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,

...

)

View solution in original post

5 Replies
Not applicable
Author

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

avg(aggr(AggrExpr1,AggrDim))

also gives me only 1 result.

Not applicable
Author

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.

swuehl
MVP
MVP

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,

...

)

Not applicable
Author

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
Author

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?