topic Re: Set Expression Avg Distinct in QlikView

Set Expression Avg Distinct

jonathanrichey — Sat, 16 Nov 2024 05:12:08 GMT

Is it possible to calculate an average within a set expression? I would like to calculate the average number of active employees for the current selection.
I am calculating the active EE by =num(COUNT({<PeriodType={'Current'},isActiveEmp_flg={1}>}DISTINCT Employee_Number),'#,##0')
After updating my expression to include Avg, I do not return any results. Is this due to aggregate and non aggregate?

Re: Set Expression Avg Distinct

OmarBenSalem — Mon, 19 Nov 2018 13:47:09 GMT

How are u calculating the AVG?

u can't use 2 agg functions at the same time ; to use the avg, ur expression should be as follow:

avg(aggr(count(measure),dimension))

Re: Set Expression Avg Distinct

sunny_talwar — Mon, 19 Nov 2018 13:49:01 GMT

What exactly are you hoping to see when using the Avg? Is it a single number across all the AsOfPeriod? and what is the exact expression you have tried?

Re: Set Expression Avg Distinct

Anil_Babu_Samineni — Mon, 19 Nov 2018 13:51:05 GMT

If i understand correctly, You can define as

num(Avg(Aggr(COUNT({<PeriodType={'Current'},isActiveEmp_flg={1}>}DISTINCT Employee_Number),AsOfPeriod)),'#,##0')

Re: Set Expression Avg Distinct

jonathanrichey — Mon, 19 Nov 2018 13:54:59 GMT

Thanks for the quick reply!

I was calculating the average by:

avg(num(COUNT({<PeriodType={'Current'},isActiveEmp_flg={1}>}DISTINCT Employee_Number),'#,##0'))