Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank over Aggregation

I am attempting to show the ranks across multiple products for a customer where only that single customer is selected

Sample data:

Customer 1Product1100
Customer 2Product1150
Customer 3Product1200
Customer 1Product215
Customer 2Product220
Customer 3Product225
Customer 1Product375
Customer 2Product350
Customer 3Product330
Customer 1Product410
Customer 2Product411
Customer 3Product412

With a selection on customer on customer 1 ... I would want my final chart (straight table) to show

Product 1: 3, 100

Product 2: 3, 15

Product 3: 1, 75

Product 4: 3, 10

Such that the answer is the ranking for the product across all customers but only the selected customers values (rank and amount) show in the table.

The closest I can find is =Aggr(Rank(Sum({<Customer=>}Amount)),Product) but this gives me the rank for each product in my main table not the rank for the customer within each rank in its implied subtable.

Help appreciated.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

=aggr( rank(sum({<Customer=>}Value)),Product,Customer)

See also attached.

View solution in original post

2 Replies
swuehl
MVP
MVP

Try

=aggr( rank(sum({<Customer=>}Value)),Product,Customer)

See also attached.

Not applicable
Author

Thanks muchly swuehl, nice and obvious. I think one of the reasons that I didn't work that out was I had that formula and then thought it wasn't working when the reason it didn't appear to be was because I didn't have a specific customer actually selected.

Thanks again.