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

[vishsaggi]

What is 33 here in your expr?  =933/33

[mrbqlik2009]

Top 3 price are 1200,900,700. sum of this 3 number is 2800.

2800 divided by 3 is 933.33.

[tresesco]

Like this?

[tresesco]

Or, if you want without selection, try using aggr() like:

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

[mrbqlik2009]

It not work correctly.

I want display 933.33 in column for Book1 with select customer.

[mrbqlik2009]

not work.

[mrbqlik2009]

I share my sample data.

I want when i select customer for example A1, display 933.33 for Book1 and 1333.33 for Book2.

[tresesco]

If you don't have at least three data points for a combination, max(,3) would return null and hence it would not work. Rather try with rank() like:

Avg( If( Aggr( NODISTINCT Rank(Price), CustomerName,ProductTitle)<=3, Price))

[mrbqlik2009]

not correct

Avg( If( Aggr( NODISTINCT Rank(Price), CustomerName,ProductTitle)<=3, Price)) return 700 for Book1 and 1000 for Book2.

I have 6 customer for Book1 and Book2, then i have at least 3 data point.

if possible please help me because it's important for me