Use of aggr() function combined with a count

[#1]

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!

[maxgro]

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

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

[MK_QSL]

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

Or

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