Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Calculate average of sum of top n

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Calculate average of sum of top n

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

View solution in original post

17 Replies
Highlighted
Champion III
Champion III

Re: Calculate average of sum of top n

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

Highlighted
Contributor III
Contributor III

Re: Calculate average of sum of top n

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

2800 divided by 3 is 933.33.

Highlighted
MVP
MVP

Re: Calculate average of sum of top n

Like this?

Capture.JPG

Highlighted
MVP
MVP

Re: Calculate average of sum of top n

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

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

Capture.JPG

Highlighted
Contributor III
Contributor III

Re: Calculate average of sum of top n

It not work correctly.

Untitled-3 copy.png

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

Highlighted
Contributor III
Contributor III

Re: Calculate average of sum of top n

not work.

Untitled-5 copy.png

Highlighted
Contributor III
Contributor III

Re: Calculate average of sum of top n

I share my sample data.

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

Highlighted
MVP
MVP

Re: Calculate average of sum of top n

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))

Highlighted
Contributor III
Contributor III

Re: Calculate average of sum of top n

not correct

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

Untitled-7 copy.png


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