topic Re: Ave Function in QlikView

Ave Function

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

Hello

I have this table: I want to know the average quantity of top two customers by item number, how I could do this?

item number	Q	Customer
100	22	A
101	15	B
102	23	C
103	19	D
104	16	A
105	10	B
106	25	C
107	16	D
108	16	A
109	22	B
110	20	C
100	12	D
101	20	A
102	24	B
103	21	C
104	22	D
105	10	A
106	14	B
107	18	C
108	15	D
109	25	A
110	14	B
100	23	C
101	21	D
102	10	A
103	14	B
104	19	C
105	19	D
106	12	A
107	22	B
108	18	C
109	20	D
110	10	A
100	16	B
101	10	C
102	24	D
103	15	A
104	23	B
105	21	C
106	22	D
107	25	A
108	10	B
109	16	C
110	12	D
100	23	A
101	11	B
102	10	C
103	11	D
104	15	A
105	12	B
106	15	C
107	23	D
108	18	A
109	24	B
110	22	C
100	10	D
101	11	A
102	12	B
103	16	C
104	21	D
105	17	A
106	21	B
107	25	C
108	24	D
109	14	A
110	20	B
100	13	C
101	15	D
102	22	A
103	15	B
104	16	C
105	24	D
106	17	A
107	22	B
108	25	C
109	14	D
110	14	A

for example the answer for item number 100 and 101 should be:

CUSTOMER	Item number	q	Ave Top 2 customer
A	100	45	23
A	101	31	20.5

Re: Ave Function

Anil_Babu_Samineni — Sat, 23 Dec 2017 08:37:33 GMT

May be this?

Avg({<Customer = {"=Rank(Sum(Q))<=2"}>} Q)

Re: Ave Function

Anonymous — Sat, 23 Dec 2017 08:55:01 GMT

Hi Anil Babu

It is somehow correct, but the answer for item number 101 should be 33.5, actually the sum of average. How to do this? For item number 101: (36+31)/2=33.5

Re: Ave Function

Anonymous — Sat, 23 Dec 2017 08:57:30 GMT

How to sum this Avg({<Customer = {"=Rank(Sum(Q))<=2"}>} Q) expression for each item number?

Re: Ave Function

Anonymous — Sat, 23 Dec 2017 09:02:05 GMT

Sorry, the right answer is:

CUSTOMER	Item number	q	Ave Top 2 customer
A	100	45	40.5
A	101	31	33.5

Re: Ave Function

Anil_Babu_Samineni — Sat, 23 Dec 2017 14:10:07 GMT

Perhaps this?

Dimensions are

Customer - =If(Aggr(Rank(Sum(Q)), Customer)<=2, Customer)

[Item Number] - =If(Aggr(Rank(Sum(Q)), [item number])<=2, [item number])

Expressions are

Avg(TOTAL <[item number]> Aggr(NODISTINCT Sum(TOTAL <Customer,[item number]> Q), Customer,[item number]))

Avg(TOTAL <[item number]> Aggr(Sum(TOTAL <Customer,[item number]> Q), Customer,[item number]))

Re: Ave Function

sunny_talwar — Sun, 24 Dec 2017 05:03:03 GMT

May be this

Avg(Aggr(If(Rank(Sum(Q)) <= 2, Sum(Q)), [item number], Customer))

Re: Ave Function

Anonymous — Sat, 30 Dec 2017 04:45:35 GMT

Hi Sunny

It works.

Thank you so much

Re: Ave Function

prma7799 — Sat, 30 Dec 2017 05:56:42 GMT

Please close this thread as helpful or right answer.