Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

aliafshari
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 numberQCustomer
10022A
10115B
10223C
10319D
10416A
10510B
10625C
10716D
10816A
10922B
11020C
10012D
10120A
10224B
10321C
10422D
10510A
10614B
10718C
10815D
10925A
11014B
10023C
10121D
10210A
10314B
10419C
10519D
10612A
10722B
10818C
10920D
11010A
10016B
10110C
10224D
10315A
10423B
10521C
10622D
10725A
10810B
10916C
11012D
10023A
10111B
10210C
10311D
10415A
10512B
10615C
10723D
10818A
10924B
11022C
10010D
10111A
10212B
10316C
10421D
10517A
10621B
10725C
10824D
10914A
11020B
10013C
10115D
10222A
10315B
10416C
10524D
10617A
10722B
10825C
10914D
11014A

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

    

CUSTOMERItem numberqAve Top 2 customer
A1004523
A1013120.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)
aliafshari
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

aliafshari
Contributor

Re: Ave Function

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

aliafshari
Contributor

Re: Ave Function

Sorry, the right answer is:

    

CUSTOMERItem numberqAve Top 2 customer
A1004540.5
A1013133.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)

Re: Ave Function

May be this

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

Capture.PNG

aliafshari
Contributor

Re: Ave Function

Hi Sunny

It works.

Thank you so much

prma7799
Honored Contributor III

Re: Ave Function

Please close this thread as helpful or right answer.