Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
arsalanam
Creator
Creator

Sum of Sales By Unique Primary Dimension

Trying to calculate sum of Sales by Client and Product in a Straight Chart but the requirement is to show Client only once for the highest Sales of the Product.

For example:

Client     Product     Sales

a           p1             100

c           p7              70

f            p4              65

even if Client 'a' sales for product 'p3' that exceed Clients 'c' and 'f', it should not be shown. Each Client should appear once and with the Product that has highest Sales.

Thank you.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

So you want to show the highest Sales and the product the sales was achieved per Client?

Try a straight table chart with dimension Client and two expressions:

=FirstSortedValue( aggr(Product, Client,Product), aggr(-sum(Sales),Client, Product) )

=max( aggr( sum(Sales), Client, Product))

View solution in original post

5 Replies
swuehl
MVP
MVP

So you want to show the highest Sales and the product the sales was achieved per Client?

Try a straight table chart with dimension Client and two expressions:

=FirstSortedValue( aggr(Product, Client,Product), aggr(-sum(Sales),Client, Product) )

=max( aggr( sum(Sales), Client, Product))

View solution in original post

lucasvallinoto
Creator
Creator

Hi Arsalan,

For the product, use:

Aggr( FirstSortedValue( Product, -Sales ), Client )

For the Sales, use:

Aggr( Max( Sales), Client )

The result is:

Max.jpg

Please, see attached.

Regards

arsalanam
Creator
Creator
Author

Thank you swuehl, it worked.

Can I limit this table to Clients with Top 10 highest Sales?
Not using Dimension Limits option but by adding a code in Dimension or Expression?

arsalanam
Creator
Creator
Author

Hi Lucas,

Thank you that was helpful.

swuehl
MVP
MVP

Dimension limits would make it quite easy to achieve what you want.

But without dimension limits, you can try using rank() function. Check attached version.