Ranking

[KmbrlyPC]

I am using the Multi-Row Formula to rank some results. The least amount of points gets a better ranking, so for a store with 0 points, the ranking should be "1". However, it is giving a ranking of 0 in this scenario. Here is the formula I am using.

if [Total Points] != [Row-1:Total Points] then [Row-1:Global Rank by Round] + 1 else [ROW-1:Global Rank by Round] endif

Note: For situations in which the Total Points is the same for a store, then it receives the same ranking. This appears to be working correctly.

[apathetichell]

your first entry looks at Row-1 which does not exist - because of that it returns 0 because you have the multi-row set to return 0 where value is not available (note it could be null()..).

the way around this is you wrap everything in a if row-1 = null() then 0 else...

note - you can use 0 if you don't reset the values that don't exist option to 0.

[Qiu]

@KmbrlyPC 

As @apathetichell  explained, it will start with 0.

Maybe we can modify the formula a bit.

[KmbrlyPC]

This solution didn't work because for situations in which there is a tie, for example if 3 store have 7 points each then I need the ranking # to be the same for these 3 tied stores. But it's not, it just keeps adding +1.

[KmbrlyPC]

It's not making any difference. Result is the same.

[apathetichell]

o.k. - that's a totally different formula and idea. I'd think a summarize tool would make more sense. You can then get a sort/count etc. If you can attach some mock data in excel/csv or alteryx I can do a quick workflow.

[KmbrlyPC]

Just to clarify, the formula that am currently using is correctly assigning the rank to a tie.

if [Total Points] != [Row-1:Total Points] then [Row-1:Global Rank by Round] + 1 else [ROW-1:Global Rank by Round] endif

My only issue is that when the Total Points is 0 it is assigning a ranking of 0 when the ranking should start at 1 even if the Total Points is zero. My formula does not need to be adjusted for ties - that part seems to be working fine.

Here is some mock data. Thanks for your help!

[apathetichell]

there are numerous way to do a ranking workflow - but this one was one I mocked up right now. It's set up to have the column with the highest total points be ranked at #1.

The reason multi-row wouldn't be great for ranking is that you aren't going to get a count of how many entries have that ranking. So while you can use it to increment a ranking if a number is above a certain number it won't increment by the number of entries at that position unless you already know how many entries there are at that position.

Here's a ranking worfklow using both multi-row formula tool, or generate rows can.

[apathetichell]

Hi!

You can either use my workflow (and change it from descending to sort ascending) or edit your formula as initially discussed.

you can do this:

if isempty([Row-1:Year]) then 1 elseif [Total Points] != [Row-1:Total Points] then [Row-1:Global Rank by Round] + 1 else [ROW-1:Global Rank by Round] endif

or change to null and test the first row for null().

If you do decide you want multiple entries accounted for in the aggregate order listing - you won't want to use a multi-flow without a specific count field.

[KmbrlyPC]

I am looking at your workflow now and one thing I am confused on is in the Select Tool why the RowCount is renamed as "Rank", but then in the next tool, in Summarize this field is not used. The RecordID field is used and renamed as "Rank'.