topic Re: Exclude selection in expression in QlikView

Exclude selection in expression

francis_gr — Mon, 04 Feb 2019 18:54:06 GMT

Hi!

In my attached file, i have a expression (avg sales/pto) where i calculate the average sales for each position.

When i select a name, the value of the sales average for position (pto) is lost and match the person sales. I need that if i select a name, the average sales value for the position don`t change.

Thanks in advance

Re: Exclude selection in expression

aworrall — Mon, 04 Feb 2019 19:00:44 GMT

Hi. You can use set analysis in a aggregate field and insert your field name and an equals sign to ignore any selection made in that field. Eg Sum({<[person name]=>}[Total Value]}

Re: Exclude selection in expression

sunny_talwar — Mon, 04 Feb 2019 19:03:19 GMT

Change your avg sales/pto expression to this

avg(total <pto> {<name>} sales) * avg(1)

Re: Exclude selection in expression

francis_gr — Mon, 04 Feb 2019 19:11:57 GMT

Hi Sunny, thanks for your fast reply. That´s works fine.

I have one question

What exactly does avg(1)?

thanks for help

regards

Re: Exclude selection in expression

sunny_talwar — Mon, 04 Feb 2019 19:16:08 GMT

Avg(1) will equal to 1 for selection and will equal to 0 for out of selection. So, when you select a name, Avg(1) = 1 for the specific name and becomes 0 for the others. This is needed because we are ignoring selection in Name field for the expression so that the avg is calculated regardless of selection in name field. and then multiplying it with Avg(1) forces the expression to just show the name selected.

Re: Exclude selection in expression

francis_gr — Mon, 04 Feb 2019 19:22:11 GMT

Ok!

Thanks for the explanation.

Re: Exclude selection in expression

francis_gr — Mon, 04 Feb 2019 19:28:02 GMT

Hi Aworrall!

I really appreciate your interest in help!

Regards