Avg of count distinct

[janvandernagel]

Hello,

I use a Count( DISTINCT Date) to calculate the unique number of days per month that a truck is used.

I get this result:

Truck 101 => 12 days
Truck 102 => 27 days
Total          => 27 days

I would like to get the average of 12+27= 19.5

I've tried the next, but with the same 27 as result:

Avg(
        Aggr(
                   Count(DISTINCT Date),Month
                   )
        )

Can anyone help me out?

Thanks in advance

Jan

[sunny_talwar · Accepted solution]

Try this may be

Avg(
        Aggr(
                   Count(DISTINCT Date), Month, Truck
                   )
        )

[janvandernagel]

Thats it!

Thanks a lot