Calculate average of sum of top n

[mrbqlik2009]

Hi.

I have sample data in QlikView such as bellow:

LOAD * INLINE [

CustomerName, ProductTitle, Fee, Quantity, Price

A1,Book1,100,7,700

A1,Book2,200,5,1000

B1,Book1,100,3,300

B1,Book2,200,6,1200

C1,Book1,100,9,900

C1,Book2,200,9,1800

D1,Book1,100,4,400

D1,Book2,200,5,1000

E1,Book1,100,6,600

E1,Book2,200,6,1200

F1,Book1,100,12,1200

F1,Book2,200,9,1800

];

I want calculate average sum of Price of top 3 customer that buy more for specific product.

for example:

average of Price of top 3 customer, for product "Book1" is 933.33.

Because:

F1 -> Book1 -> 1200

C1 -> Book1 -> 900

A1 -> Book1 -> 700

(1200+900+700)/3=933.33

[tresesco]

What is your expected output ( for first four lines in your chart) ?

[mrbqlik2009]

when i select  for example customer "A1", in chart 2 line display. first line for product "Book1" and second line for product "Book2".

My expected output is display 933.33 for Book1 and 1333.33 for Book2 in avg column.

933.33 is average price of top 3 for book1 and 1333.33 is average price of top 3 for book2.

for all customer for book1 and book2 product these number is fixed.

[tresesco]

Try:

Avg( total <ProductTitle> If( Price >=Aggr( NODISTINCT Max(Price,3), ProductTitle), Price))

[mrbqlik2009]

Its true for all customer. but when i select 1 customer it not display. I want display avg when i select 1 customer.

[tresesco · Accepted solution]

Avg({<CustomerName>} total <ProductTitle> If( Price >=Aggr( NODISTINCT Max({<CustomerName>} Price,3), ProductTitle), Price))*avg(1)

[mrbqlik2009]

Thank you.

It solved.

[mrbqlik2009]

how about when we have multi record of customer for a product.

for example 3 record of all customer for product book1.

it mean we want use sum(price) instead of price.

[mrbqlik2009]

It solved.

I use aggr(sum({<CustomerName>} Price) insted Price.