topic Re: Distinct count and Avg in App Development

Distinct count and Avg

beat_roos — Mon, 20 Dec 2021 21:38:40 GMT

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.

Re: Distinct count and Avg

Kushal_Chawda — Wed, 18 Dec 2019 09:54:02 GMT

To use nested aggregation you need to use Aggr function

try like below

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

Re: Distinct count and Avg

beat_roos — Wed, 18 Dec 2019 09:58:54 GMT

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.

Re: Distinct count and Avg

Kushal_Chawda — Wed, 18 Dec 2019 11:01:34 GMT

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

Re: Distinct count and Avg

beat_roos — Wed, 18 Dec 2019 13:38:21 GMT

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