topic Re: Average excluding selection in QlikView

Average excluding selection

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

Hi, this is my data set

A B $

20 black 200

20 red 1000

30 white 10

70 blue 200

80 blue 300

90 blue 100

93 black 50

95 red 100

..........

i've a bar chart with my $ and i need to calculate avg of $ using A and B as dimension. No problem

Sales department (who hates me) asks to add a futher field with an avg for all A field with the same B field and if i select A=70 my average must be calculate without selected (80 blue + 90 blue).

Help me pls, tks

Re: Average excluding selection

sunny_talwar — Mon, 21 Mar 2016 11:11:24 GMT

May be this:

Avg({<A, B = p(B)>}$)

Re: Average excluding selection

t_chetirbok — Mon, 21 Mar 2016 11:53:19 GMT

hi!

if you select A=70, then result must be like this?

Re: Average excluding selection

canmarroig — Mon, 21 Mar 2016 12:07:24 GMT

No Tatsiana, my result must be:

80 blue

90 blue

Re: Average excluding selection

t_chetirbok — Mon, 21 Mar 2016 12:13:06 GMT

I think Sunny ' s result is correct, isn't it?

Re: Average excluding selection

canmarroig — Mon, 21 Mar 2016 12:13:49 GMT

Perhaps this: Avg({<A=e(A), B=p(B)>}$) but I've always to select an A dimension to obtain result.

Re: Average excluding selection

sunny_talwar — Mon, 21 Mar 2016 12:14:05 GMT

May be one of the two:

1) Avg({<A, B = p(B)>} Dol)

2) Avg({<A = e(A), B = p(B)>} Dol)

Re: Average excluding selection

tresB — Mon, 21 Mar 2016 12:24:16 GMT

Like this?

Re: Average excluding selection

sunny_talwar — Mon, 21 Mar 2016 12:25:15 GMT

tresesco‌ what expression are you using?

Re: Average excluding selection

tresB — Mon, 21 Mar 2016 12:26:48 GMT

Avg({<A, B = p(B)>-<A=p(A)>}Dollar)

Equivalent to yours.

Re: Average excluding selection

sunny_talwar — Mon, 21 Mar 2016 12:29:33 GMT

Well we def. are doing something similar, but I wanted to know what is another way of doing it. Thanks for sharing