Use of aggr() function combined with a count

Wed, 18 Feb 2015 14:46:45 GMT

Hi all,

Bit of a tricky one (for me anyway) here, if some genius could help?

My initial source table is as follows:

field1 field2 field3

A abc1 a

A abc2 a

A abc1 a

A abc3 b

B abc1 a

B abc2 a

B abc3 a

B abc4 a

I'm trying to get to a single figure which calculates the average of the count of field2 (must be distinct for every field1), where field3 = a, by field1....I probably have breached the english language there so I'll break it down below:

I.e. the resulting figure should be logically calculated by the following:

Count unique field2 values, for each field1 (i.e. for A, there are 2 unique field2 values where field3 = a. The third row is a duplicate field2 value, and the fourth row has field3 = b so doesn't count).

Result:

A 2

B 4

Then calculate the average of these: = 3

Just want to display this number, regardless of the filters being applied to the dashboard.

Thanks!

Re: Use of aggr() function combined with a count

maxgro — Wed, 18 Feb 2015 22:57:33 GMT

avg(aggr(count({$ <field3={a}>} DISTINCT field2), field1))

avg({1} aggr(count({1 <field3={a}>} DISTINCT field2), field1))

Re: Use of aggr() function combined with a count

MK_QSL — Wed, 18 Feb 2015 23:06:36 GMT

=Avg (Aggr (Count DISTINCT({<field3 = {'a'}>}field2),field1))

=Avg ({1}Aggr (Count DISTINCT({1<field3 = {'a'}>}field2),field1))

topic Re: Use of aggr() function combined with a count in QlikView

Use of aggr() function combined with a count

Re: Use of aggr() function combined with a count

Re: Use of aggr() function combined with a count