RANK Function Nexel

I have country names in the columns and individual products in the rows. My cube returns the number of products sold per country.
Now I want it to return, for each product, the rank for country X (only for country X; the other countries are not relevant here).

I have reached the point where I can use the RANK formula. However, when I use
RANK([@b;COUNTRYX]; Range.Right) I do not get any values.

Find more posts tagged with

Accepted answers

All comments

Yuliya Berezutskiy

hi Marco,

the common variations you might want to try are:

RANK([@b ;COUNTRYX]; Range.Row) - Rank across the whole row

RANK([@b ;COUNTRYX]; Range.Down) - Rank vertically (column-wise)

Marco S

Hello Yuliya,

thanks for your solution proposal. Unfortunately, the formula doesn’t work. I only get zeros.

Robert Barkaway

Hi Marco, your syntax should look something like this:

That's working to rank each value of block A, if you want yours to always reference a specific row as it sounds like then you can just amend the first bit to reference the right row. Hopefully that helps.

Marco S

Okay, that's helping a lot, thank you. So now i have following syntax:

=RANK([@d;*;COUNTRYX],[@d;*;*;Range.Horizontal]

However, now in each row there are only values, or only 1s, where I don’t have any values for @d.

Robert Barkaway

Okay, that makes sense, I get the same if I do a horizontal one and have empty rows

Logically that makes sense as if a row is blank then any cell in it will be ranked joint first, what result are you trying to achieve?

Marco S

What i want to achive:

For every product, I want the rank of Country A. In the end, I will have a list of 75 products, and then I want to sort or filter all products where Country A has rank 3.

Robert Barkaway

Ah okay, well that's basically what the example I showed is doing, you just need to also validate that the row isn't blank, in my example it would be

=if(sum([@A ;*;*;Range.Horizontal])>0,RANK([@A ;*;BS1000],[@A ;*;*;Range.Horizontal]),0)

That will return a zero where the row is blank (you could also use -1 or something if you prefer) or the rank of my chosen tuple where it isn't.

The problem you'll have will be in filtering, Nexel is executed after the layout so you won't be able to use the layout filter, and I don't believe there's a way to filter with Nexel (unless somebody can tell me different).

Marco S

Could it be a problem, that the numbers I am ranking are allready Nexel formulas? Unfortunately I don't get any values, only empty cells. When I make the range vertical, I do get numbers, but they are not always correct.

Robert Barkaway

That shouldn't be a problem, I just tested adding a second Nexel block on mine and calculating the ranking from that and it works fine

Can you post some screenshots of your layout and Nexel and I can try and see if there's an issue?