Multi Row Formula, [row-1] for first row

Hi,

I am trying to calculate rank using multi row formula tool. I want to assign same rank to same score, and all ranks should be consecutive (No missed numbers) (Similar to Golf)

I am using this calculation -

If [Count]<[Row-1:count]
then ([Row-1:Rank2] +1)
else [Row-1:Rank2]
endif

It is giving me desired results, but it's starting from 0. I need to start it from 1.

Selecting "Set Values of closest valid row" for values for rows that don't exist should do the trick? But it's not. I think I understand it wrongly. Can anyone please clarify what this really means? And how to achieve this?

Any help would be great. Attching workflow for reference

Thank you!

MultiRowFormula.yxmd

Reporting

Output

Common Use Cases

Accepted answers

patrick_digan

@rs1 See attached workflow with both of my answers. You could also use the tile tool.

MultiRowFormula.yxmd

All comments

patrick_digan

@rs1 I would try 1 of these two things:

1) Apply a min max of 1

max(1,
If [Count]<[Row-1:count]
then ([Row-1:Rank2] +1)
else [Row-1:Rank2]
endif)

2) Set the don't exists to "null" and then set nulls to 1

If isempty([Row-1:Rank2]) then 1 
elseIf [Count]<[Row-1:count]
then ([Row-1:Rank2] +1)
else [Row-1:Rank2]
endif

patrick_digan

@rs1 See attached workflow with both of my answers. You could also use the tile tool.

MultiRowFormula.yxmd

sgwong

Is it feasible using multiple rows formula to obtain from the input to output?

AlteryxData.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335