Skip to main content

 

Hello Pigment,

I would like to ask for your help regarding the RANK function, as I don’t fully understand the logic behind the ordering of its rank.

Initially, I thought it considered the order of the list used as a dimension; that is, if my first 5 values to rank are equal, my rank 1 would be the first item in the list. However, this doesn’t always work that way. Here’s an example:

 

Ideally, I want to create something like "ISFIRSTOCCURRENCE" that allows me to identify the first value created (like in Anaplan). My initial approach was essentially that: if my rank value is 1, then that is my first occurrence. However, I can’t achieve this correctly.

What is the logic behind using "Sequential" as argument then? How does it behave? Or how does it find the values to rank? Do they rely on the ID behind the list items when the ranking attributes are the same?

I found a post where they created a list of values to rank and used BY FIRSTOCCURRENCE, but that would require creating the list every time I have new records, which is exactly what this method aims to avoid—creating data that ends up being useless and preventing errors during import.

My expectation would be able to do this:
 

Any advice?
Thanks in Advance.

Alberto Garneri

Hi Alberto,

 

In Pigment the RANK function assigns ranks to values within a list or metric based on their size relative to other values. When using the 'Sequential' argument, it will rank values in the order they appear. It's important to note that the RANK function does not consider the order of the list used as a dimension.

In this case, the workaround would be to create a list of values to rank them and use function BY FIRSTNONBLANK.

Let us know if that helps.

 

Hello @Gabriel Amaro da Luz 

Really appreciate your response, but my question remains still; if RANK is not considering the order of the list used as a dimension, based on what Ranks when multiple ranking items are the same? What it is considering? What is the logic behind? 

Alberto Garneri

 

Hi @A_Garneri,

I hope you are doing well.

When multiple items have the same value in the dimension being ranked, Pigment's RANK function resolves ties by relying on the order of the items in the dataset—but only after the main ranking criteria (like values in the BY dimension) are considered.
For example, if "12345" and "56789" have the same value in the ranking dimension, Pigment might use the natural order in which the products were listed in the dataset to assign the final ranks.

I hope this helps.
Thanks!

Reply


Ask your question here!

Terms & ConditionsCookie settings