Total average by field, without chart dimension

JustInTime — Sat, 16 Nov 2024 17:58:36 GMT

Hi everyone,

I've got an annoying problem that I've managed to solve in some cases but doesn't fit specifically what I'm trying to do.

I have data that looks like the following:

ID	Period	Numerator
1	201804	4
1	201804	5
1	201804	7
1	201805	4
2	201806	14
2	201806	15
2	201807	17
2	201807	14

And I want to transform it into the following:

Period	Average of Numerator
201804	4.666...
201805	4.666...
201806	15
201807	15

Where Average of Numerator is the average of all Periods for each ID.

I have accomplished this with the following expression:
avg(total <ID> aggr(avg(Numerator), Period))

But in order for this to work I need to have ID as a dimension in the chart. I don't want to have ID as a dimension because I will be using this expression in a line graph alongside other expressions. If I use a line graph with other expressions, the change in average according to the ID doesn't happen.

I have attached a test file and test data that hopefully shows what I mean.

Re: Total average by field, without chart dimension

Taoufiq_Zarra — Tue, 13 Oct 2020 20:26:08 GMT

@JustInTime why is 4.666 not 5

If I understood correctly you need

dimension : Periond

measure :

=aggr(nodistinct avg( Numerator),ID)

output:

Re: Total average by field, without chart dimension

JustInTime — Wed, 14 Oct 2020 08:12:35 GMT

Amazing, thank you. This worked, using nodistinct was the key.

topic Re: Total average by field, without chart dimension in QlikView

Total average by field, without chart dimension

Re: Total average by field, without chart dimension

Re: Total average by field, without chart dimension