topic Re: average of count distinct in QlikView

average of count distinct

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 — Thu, 03 Aug 2017 12:49:22 GMT

did you tried like below?

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

sunny_talwar — Thu, 03 Aug 2017 12:52:02 GMT

May be you forgot to add PickDate

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

Anonymous — Thu, 03 Aug 2017 12:52:11 GMT

the calculation is not working, showing no result 😕

Anonymous — Thu, 03 Aug 2017 12:54:58 GMT

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 — Thu, 03 Aug 2017 13:06:49 GMT

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 — Thu, 03 Aug 2017 13:08:03 GMT

Try this

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

Anonymous — Thu, 03 Aug 2017 13:10:59 GMT

thanks for the quick reply!

now it works perfectly !!

much appreciated