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]

I mocked that up quickly to get it over to you - so ignore the column headings there, I was exploring different ideas as I went. I think the summarize/count multi-row formula option also in the workflow is more succinct.

[KmbrlyPC]

Yes, thank you. I think the multi-row formula would be the simplest path because I am needing to do 9 different ranks for various groups. The fewer tools, the better.

[apathetichell]

Yes - may I recommend using a transpose and then using multi-row set on group-by. You can use the same strategy where you summarize the counts by value and group by your 9 different fields. you can then group by the 9 different fields and you can use one multi-row to get the ranks for each.

[KmbrlyPC]

It would be very helpful if you could provide an example workflow with the mock data attached. I am needing to do a

global Store Rank (meaning all regions and areas combined) by:

Year,

Quarter,

Round.

Then I am needing to do a Store Region rank by:

Year

Quarter

Round

Last, a Store Area rank by:

Year

Quarter

Round

The lower number of points, the better the rank. So 0 points should have a Ranking of 1. In the end, I am needing 9 columns of different ranks together in the same table

[apathetichell]

so this is your base workflow - let's see what I can do to automate the others...

[apathetichell · Accepted solution]

This is where I'm at basically to continue this process you can recreate the workflow for the 3 remaining subgroups and then union everything together.

Yes - I'm sure there is a better way to automate this but with the swap between one group-by in summarize to two I couldn't think of it on the fly.

note - with the limited amount of data I can't see any difference in rankings between the metrics which definitely prevents a certain level of error checking.

[KmbrlyPC]

Thank you so much! You've been super helpful!