Distinct count and Avg

[beat_roos]

Hi Community 

For a new app I need to count the number of unique Members. I do it by a distinct count: 
Count(distinct [Opp. Kontaktrollen: ID])
what works very good and I create a master key figure for that.

Next idea was to know the ave number of members. So I guess to calc an avg value by using this master key figure but I didn't found a way to calc with an existing key figure instead of a field. 

So next idea to just add the avg function to my calc like:
avg(Count(distinct [Opp. Kontaktrollen: ID]))
but now I get an error because nested aggregations are not allowed. 

Can anyone help me to solve that?
I like to know (depending on the selection) what the avg number of unique members of my field Kontaktrollen:ID is. 

[Kushal_Chawda · Accepted solution]

To use nested aggregation you need to use Aggr function

try like below

avg(aggr(Count(distinct [Opp. Kontaktrollen: ID]),YourDimension))

[beat_roos]

Thanks a lot. i am not sure what do you mean with "yourDimension"? There is no other dimension I guess. I just want to calc the average number of members and for that I count the ID Dimension.  

[Kushal_Chawda]

First Aggregation function will always give you single value without using any dimension and taking Avg of that single value doesn't make any sense. 

let's say I have dataset like below

Load * inline [

ID, Name

1,a

1,b

2,a

2,c

3,a

3,b

4,b ];

if I use count (distinct ID) I will get 4 (as distinct  values are 1,2,3,4). Now you can't take Avg for single value returned from first aggregation.

But If you want to take Avg against some dimension then that is absolutely possible using Aggr function like below

Avg(aggr(Count(distinct ID),Name))

Above expression works as follows

1) Qlik internally creates a table by aggregating count for each value of Name

Name,CountID

a,3

b,2

c,1

 2) Qlik then calculates Avg value on top of the table created in step 1) for CountID as 2 

This how nested aggregation works in Qlik. So it's important to consider dimension against which you want to perform nested aggregation.

If you can provide some sample data with expected output then it will be helpful

[beat_roos]

totally agree. Now I understand it 🙂 Thank you very much for your fast and kind help!