Rank with multi row formula tool

[mphillips27]

I have a column I would like to rank -- if the values are the same I would like them to be the same rank

 I am using a multi row formula tool:

if [recordables] != [Row-1:recordables] then [Row-1:recordables rank] + 1 else [ROW-1:recordables rank] endif

My issue is that is that the tool is not accounting for zero. -- if the recordables are zero I would like the rank to start at 1, but the tool starts it at zero.

Recordables	Rank	Desired rank
0	0	1
0	0	1
1	1	2
1	1	2
2	2	3

[Luke_C · Accepted solution]

Hi @mphillips27 

Here's two options:

1. Multirow formula option
   1. if isnull([Row-1:Recordables]) then 1 elseif [Recordables]=[Row-1:Recordables] then [Row-1:Rank] else [Row-1:Rank]+1 endif
2. Tile tool based on unique recordables

[Maskell_Rascal · Accepted solution]

Hi @mphillips27 

You were really close on your formula, but just need to add in one more variable to get the first rows to start at a rank of 1. When you are referencing the negative row of a numerical field, Alteryx identifies the value as zero. So in your case, [recordables] != [Row-1:recordables] is false because its reading it as 0=0. 

This formula should work for you instead. 

IF [Row-1:recordables rank]=0 THEN 1 
ELSEIF [recordables]!=[Row-1:recordables]
THEN [Row-1:recordables rank]+1
ELSE [Row-1:recordables rank] 
ENDIF

Cheers!

Phil

[mphillips27]

Both worked, thank you!