# New to QlikView

Discussion board where members can get started with QlikView.

Contributor

## Ave Function

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
Tags (1)
8 Replies

## Re: Ave Function

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor

## Re: Ave Function

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

Contributor

## Re: Ave Function

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

Contributor

## Re: Ave Function

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

## Re: Ave Function

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]))

OR

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP

## Re: Ave Function

May be this

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

Contributor

## Re: Ave Function

Hi Sunny

It works.

Thank you so much

Honored Contributor III