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