topic Rank function in straight table with multiple dimensions in QlikView

Rank function in straight table with multiple dimensions

Thu, 30 Mar 2017 10:32:33 GMT

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

sunny_talwar — Thu, 30 Mar 2017 10:56:02 GMT

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

Thu, 30 Mar 2017 12:02:31 GMT

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

sunny_talwar — Thu, 30 Mar 2017 12:05:07 GMT

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

Thu, 30 Mar 2017 12:08:29 GMT

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 — Thu, 30 Mar 2017 12:10:28 GMT

May be this

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

Mon, 03 Apr 2017 11:26:52 GMT

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)

Mon, 03 Apr 2017 11:28:12 GMT

Refer to this post for detailed information about rank function.

sunny_talwar — Mon, 03 Apr 2017 11:29:34 GMT

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