- RANKX is a function that lets you evaluate the ranking
of an expression inside a lookup table
that is built by RANKX itself.
You need to provide RANKX first the table
that you want to iterate and then an expression
that is evaluated during the iteration
in order to build the lookup table.
RANKX will then evaluate the same expression again
in the filter context where it was called
in order to compute a value.
Finally, it searches for the position
of the final value that it computed inside the lookup table.
And the position is the value returned by RANKX.
This is especially useful whenever you want
to produce ranking of dynamically computed tables.
Let's see a few examples
because RANKX accepts many more arguments
but it's better to look at them with examples.
In this first example, I'm using RANKX to rank the amount
against all the different brands.
So I first build a table, brands and sales,
that contains the VALUES of the product brand
and I compute in the amt variable the value of sales amount.
Finally, I use ADDCOLUMNS to add a new column
to the same variable that contains rank
that calls RANKX over brands and sales of amount.
RANKX will build a lookup table using brands and sales
and compute the amount for every row.
Amount is the value computed earlier.
Finally, it will compute amount again
in the row context generated by ADDCOLUMNS.
That will contain amount for the current row
and the ranking is the position of the current brand
in the list of all the brands.
As you see, we have one, two, three, four, five,
six, seven, all the numbers ranked depending
on the value of amount.
You can use a third argument of RANKX
in case the expression that you want to use
when building the lookup table is different
than the expression that you want to compute
in order to find the position.
For example, here instead of ranking a table against itself,
I have a sales levels table that contains values,
six million, three million and so on.
And contains a column, limit.
I want to rank the amount of sales of the current brand
against this sales levels table.
Therefore, when building the lookup table,
I will use the limit column.
But when I compute the value
that I want to search into the lookup table,
I need to use sales amount for the current brand.
So the second and the third argument are different.
The second argument is used when building the lookup table
and the third argument is used in order
to compute the value to search for inside the lookup table.
There is a fourth argument that you can provide to RANKX
that is the sort order.
It can be ascending or descending.
By default, RANKX provides a lower value
to the highest number,
so a number with a very large value will be ranked first.
And this is typically for sales amount, for example.
But if you want the opposite,
you can change the default behavior.
Here, I went back to the first example.
I use VALUES of product brand and amount
and I rank brands and sales ascending or descending.
You see that ascending, I get 11 for seven million,
which is the highest value.
And descending, which is the default,
ranks the same expression as one.
So the two ranked ascending and descending
provide opposite values.
And you need to choose the one that best fit your needs.
Finally, the last argument of RANKX
is the behavior that RANKX need to have
when there are ties.
There are two possible options, SKIP or DENSE.
By default, it uses SKIP,
meaning that ties are counted
and the rank increases even in the presence of ties.
But you can use DENSE in order to not count ties
and rank all of them together,
increasing only by one the value.
Let's see an example.
In order to introduce ties inside my calculation,
instead of using just sales amount,
here I'm using MROUND of sales amount
in order to round the value to the nearest million.
And then I rank
against brands and sales amount against itself,
the first time using SKIP, which is the default value
and the second time using DENSE.
You see that RANKX SKIP is the same up to the first
set of ties but then because three million is a tie,
rank SKIP goes from four, four and then six
because it counted Proseware as one of the possible value
even though it ranked it only four.
Rank DENSE instead grows only by ones.
So from four, jumps to five and then to six,
whereas rank SKIP already reached eight.
So the order of the values returned
by both RANKX calculations is the same
but the values are different
and you need to use the one that best fit your requirements.