I have sample data in QlikView such as bellow:
LOAD * INLINE [
CustomerName, ProductTitle, Fee, Quantity, Price
I want calculate average sum of Price of top 3 customer that buy more for specific product.
average of Price of top 3 customer, for product "Book1" is 933.33.
F1 -> Book1 -> 1200
C1 -> Book1 -> 900
A1 -> Book1 -> 700
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))
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