Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results 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)

8 Replies
MVP

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

MVP

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

MVP

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.

MVP

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

Community Browser