average of count distinct

[Anonymous]

Hello,  I didnt think I would have to ask you guys for that but im not getting it to work properly.

first table is my regular data count(distinct) per day per hour

count(DISTINCT {1<PickEvent={'PROGRESS'}>} PickLine)

second table I want to calculate, for all of my available data, the average count(distinct) per hour independent of any date selections.

avg(aggr(count(DISTINCT {1<PickEvent={'PROGRESS'}>}PickLine),PickHour))

Any help is appreciated.

regards,

Lucas

[Chanty4u]

did you tried like below?

aggr(avg(count(DISTINCT {1<PickEvent={'PROGRESS'}>}PickLine),PickHour))

[sunny_talwar]

May be you forgot to add PickDate

Avg(Aggr(Count(DISTINCT {1<PickEvent={'PROGRESS'}>}PickLine), PickDate, PickHour))

[Anonymous]

the calculation is not working, showing no result 😕

[Anonymous]

I was pretty sure i tried this and it didnt show any result.

anyways, this is working!! thanks!

Avg(Aggr(Count(DISTINCT {1<PickEvent={'PROGRESS'}>}PickLine), PickDate, PickHour))

[Anonymous]

sorry, it is still not working as intended.

the results are still varying depending on the date selection.

I thought the "1" in the set analysis would refer to the whole data set ignoring any selections 😕

[sunny_talwar · Accepted solution]

Try this

Avg({1} Aggr(Count(DISTINCT {1<PickEvent={'PROGRESS'}>}PickLine), PickDate, PickHour))

[Anonymous]

thanks for the quick reply!

now it works perfectly !!

much appreciated