Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggr error in chart: only displaying 1 value per dimension

Hey all,

I have a list of queries, each query is part of a bucket. I want to use an if statement to assign the average monthly volume of the query's bucket if its own monthly volume is null, and if it's not null just return the query's volume. Here's the formula:

if(isnull(avg([monthly volume])), aggr(avg({1}[monthly volume]), bucket_v2), avg([monthly volume]))

And the average monthly volumes by bucket are:

Capture.PNG

However, this is the result:

Capture.PNG

The column [monthly vol] is just there to show that all are null for the query except "online colleges", so the [adj monthly vol] should be the buckets' averages for all except "online colleges". As you can see it's only showing the bucket average for one of each bucket, the rest are null.

Can anyone help me figure out what I'm doing wrong?

1 Solution

Accepted Solutions
sunny_talwar

Try this

If(IsNull(Avg([monthly volume])), Aggr(NODISTINCT Avg({1}[monthly volume]), bucket_v2), Avg([monthly volume]))

or this

If(IsNull(Avg([monthly volume])), Avg(TOTAL <bucket_v2> {1}[monthly volume]), Avg([monthly volume]))

View solution in original post

4 Replies
sunny_talwar

Try this

If(IsNull(Avg([monthly volume])), Aggr(NODISTINCT Avg({1}[monthly volume]), bucket_v2), Avg([monthly volume]))

or this

If(IsNull(Avg([monthly volume])), Avg(TOTAL <bucket_v2> {1}[monthly volume]), Avg([monthly volume]))

Anonymous
Not applicable
Author

The NODISTINCT worked like a charm. Can't believe I didn't think of that. Thank you Sunny!

sunny_talwar

Glad it worked out for you, but I wonder if you tried the other one also and if it did not work?

Anonymous
Not applicable
Author

The other one worked but with the {1} set identifier included, it returns all rows disregarding my selection. When I remove the {1} it just returns nulls like before. So the first solution with NODISTINCT is better Thanks again!