Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Avg({<CustomerName>} total <ProductTitle> If( Price >=Aggr( NODISTINCT Max({<CustomerName>} Price,3), ProductTitle), Price))*avg(1)
What is 33 here in your expr? =933/33
Top 3 price are 1200,900,700. sum of this 3 number is 2800.
2800 divided by 3 is 933.33.
Like this?
Or, if you want without selection, try using aggr() like:
Avg( If( Price >=Aggr( NODISTINCT Max(Price,3), ProductTitle), Price))
It not work correctly.
I want display 933.33 in column for Book1 with select customer.
not work.
I share my sample data.
I want when i select customer for example A1, display 933.33 for Book1 and 1333.33 for Book2.
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))
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