Rank with multi row formula tool

Question

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.

RecordablesRankDesired rank0010011

121

122

23

Luke_C · Accepted Answer

Hi @mphillips27

Here's two options:

* Multirow formula option* if isnull([Row-1:Recordables]) then 1 elseif [Recordables]=[Row-1:Recordables] then [Row-1:Rank] else [Row-1:Rank]+1 endif

* Tile tool based on unique recordables

Maskell_Rascal · Accepted Answer

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