topic Aggr error in chart: only displaying 1 value per dimension in QlikView

Aggr error in chart: only displaying 1 value per dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

However, this is the result:

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?

Re: Aggr error in chart: only displaying 1 value per dimension

sunny_talwar — Wed, 17 Jan 2018 02:16:05 GMT

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]))

Re: Aggr error in chart: only displaying 1 value per dimension

Anonymous — Wed, 17 Jan 2018 17:32:07 GMT

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

Re: Aggr error in chart: only displaying 1 value per dimension

sunny_talwar — Wed, 17 Jan 2018 17:33:24 GMT

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

Re: Aggr error in chart: only displaying 1 value per dimension

Anonymous — Wed, 17 Jan 2018 17:45:20 GMT

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!