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
What is your expected output ( for first four lines in your chart) ?
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.
Try:
Avg( total <ProductTitle> If( Price >=Aggr( NODISTINCT Max(Price,3), ProductTitle), Price))
Its true for all customer. but when i select 1 customer it not display. I want display avg when i select 1 customer.
Avg({<CustomerName>} total <ProductTitle> If( Price >=Aggr( NODISTINCT Max({<CustomerName>} Price,3), ProductTitle), Price))*avg(1)
Thank you.
It solved.
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.
It solved.
I use aggr(sum({<CustomerName>} Price) insted Price.