Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank function in straight table with multiple dimensions

Hi,

I need top 5 products in a row with ',' as separator which is having highest sales and also group by another dimension volume.

I have used rank function but as table is having multiple dimensions in straight table i couldn't meet the requirement.

I have used the below expression.

=AGGR(IF(Rank(Sum(sales)<=5,product,product),volume)

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

In the below expression for rank function

4 displays Lowest rank on first row, then incremented by one for each row.

This works correct for the above requirement.

=Aggr(Concat(IF(Aggr(Rank(sum(sales), 4, 1),volume,Product) < 6, product), ', '),volume)

View solution in original post

8 Replies
sunny_talwar

Would you be able to share few lines of data and what the expected output needs to be from the sample data?

Not applicable
Author

Merging data into single cell Pivot

As in the above link, I need top 5 products with highest sales in the same way with ',' as separator

sunny_talwar

I understand that part... I am not sure how your data looks like and what you mean when you say this:

"table is having multiple dimensions in straight table"

"having highest sales and also group by another dimension volume"

I was hoping you can show how the data looks so that we can help you better

Not applicable
Author

I need products that use the "volume"(any dimension).

If there are more than 5 such products , it will list only the 5 with the highest sales

sunny_talwar

May be this

Concat(DISTINCT Aggr(If(Rank(Sum(Sales)) < 6, Product), Product, Volume), ', ')

Not applicable
Author

In the below expression for rank function

4 displays Lowest rank on first row, then incremented by one for each row.

This works correct for the above requirement.

=Aggr(Concat(IF(Aggr(Rank(sum(sales), 4, 1),volume,Product) < 6, product), ', '),volume)

Not applicable
Author

How to use rank function?

Refer to this post for detailed information about rank function.

sunny_talwar

Not sure I understand why you need the extra Aggr() function... this did not work?

Concat(IF(Aggr(Rank(sum(sales), 4, 1),volume,Product) < 6, product), ', ')

or what I provided?

Concat(DISTINCT Aggr(If(Rank(Sum(Sales)) < 6, Product), Product, Volume), ', ')