topic Re: avg expression in QlikView

avg expression

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

Hi!

I'm trying to calculate the number of employees (num) in each position (pto), Empl OK in my table but I only get Empl (number of total employees)

Year	Num	Pto	Empl (count(total <Pto> Num))	Empl OK
2017	1	com1	4	2
2017	2	com1	4	2
2017	3	com3	4	1
2017	4	com2	4	1

How can i do it?

Regards

Re: avg expression

sunny_talwar — Thu, 23 Aug 2018 11:36:26 GMT

Do you have Pto as one of the dimensions? If not, may be try this

Aggr(NODISTINCT Count(Num), Year, Pto)

Re: avg expression

eva_maria — Thu, 23 Aug 2018 11:57:22 GMT

Sunny, thanks for your qick reply,

Pto is included as chart expression and year is a calculated dimension

I have used your expression, the result is (Empl colum)

Year	Num	Pto	Empl	Empl OK
2017	1	com1	2	2
2017	2	com1	1	2
2017	3	com3	1	1
2017	4	com2	2	1

Re: avg expression

sunny_talwar — Thu, 23 Aug 2018 12:03:04 GMT

Why don't you add Pto as a dimension... and then your original expression ( Count(total <Pto> Num) ) should work

Re: avg expression

eva_maria — Thu, 23 Aug 2018 12:24:10 GMT

Please, see my attached file.

Re: avg expression

sunny_talwar — Thu, 23 Aug 2018 12:30:08 GMT

Wow, you made it really complex... try this

Dimension

Year

Num

Pto

Expression

Count({$<Year={$(=(Year)-1)}>} TOTAL <Pto> Num)

Re: avg expression

eva_maria — Thu, 23 Aug 2018 13:11:50 GMT

Sunny, no way i can match the results in your link.

Any other opcion??

Re: avg expression

sunny_talwar — Thu, 23 Aug 2018 13:18:41 GMT

What do you mean Eva? I am not sure I understand

Re: avg expression

eva_maria — Thu, 23 Aug 2018 17:38:12 GMT

Hi,

I think it can't be done with Num as a dimension, so i have removed it and that's what i get

Thank you for your patience and interest in helping.

Regards

Re: avg expression

sunny_talwar — Thu, 23 Aug 2018 17:40:53 GMT

But did I just not show you how this can be done?

What is wrong with this?

Re: avg expression

eva_maria — Thu, 23 Aug 2018 18:48:17 GMT

You`re right Sunny, Accepts my appology. I had not correctly applied your indications

Your solution works fine.

Thanks for help