Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ranking

KmbrlyPC
8 - Asteroid

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.

 

Rank.JPG

16 REPLIES 16
apathetichell
18 - Pollux

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
8 - Asteroid

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
18 - Pollux

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
8 - Asteroid

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
18 - Pollux

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

apathetichell
18 - Pollux

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
8 - Asteroid

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

Labels