Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

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

How to make Rank(same ranking but next rank is +1)

SaoriYamguchi
8 - Asteroid

Hi
I want to make a rank.
I can make a same(Group) rank.
But, I want to next rank is not "+1" rank.

Example

NameScoreRank
Mike1001
Sarry2002
Emi2002
Koji3004

 

How can I make it?

11 REPLIES 11
BenMoss
ACE Emeritus
ACE Emeritus

To achieve this you can use a couple of multi-row formula tools stitched together.

 

The first one to calculate the running number of 'ties' and the second then uses this field to create the rank.

 

IF [Row-1:Score] = [Score] THEN [Row-1:Ties]+1 ELSE 0 ENDIF

 

IF [Row-1:Ties]>0 AND [Row-1:Score] != [Score] THEN [Row-1:Rank]+1+[Row-1:Ties] ELSEIF [Row-1:Score]=[Score] THEN [Row-1:Rank] ELSE [Row-1:Rank]+1 ENDIF

Example attached based on your sample data...

 

2019-03-13_08-21-23.png

 

Ben

SaoriYamguchi
8 - Asteroid

Thank you!

I got it!!!!!!

ThizViz
11 - Bolide

Just want to say thank you, Ben, for the screenshots.

 

Very helpful for those of us searching for tutorials!

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
DHB
8 - Asteroid

That's awesome but I've run into a problem when I try to keep the rankings to within groups.  Do you know how I might be able to fix it?

 

I used the same 2 tools and ticked the Group by 'Group' box but get this when I get to the second group;

 

GROUPACTIVE_DAYSTiesRankWhat the Rank Should Be
A2011
A2111
A5033
A5133
A6055
A6155
A7077
A7177
A7277
A801010
A811010
A901212
A1001313
A1011313
A1101515
A1501616
A1511616
A1801818
A1811818
A2302020
A3202121
B0101
B0201
B0301
B0401
B0501
B0601
B0701
B0801
B1099
B1199
B201111
B211111
B301313
B311313
B501515
B601616
B611616
B801818
B811818
B821818
DHB
8 - Asteroid

I think I've got it.  I've added a third Multi-Row Formula tool prior to the other two to count the records per Group (course_count) and made minor adjustments to the existing two Multi-Row Formula Tools.

 

Multi-Row Formula Tool 1: [Row-1:course_count]+1

Multi-Row Formula Tool 2: IF [course_count] = 1 then 0 ELSEIF [Row-1:ACTIVE_DAYS] = [ACTIVE_DAYS] THEN [Row-1:Ties]+1 ELSE 0 ENDIF

Multi-Row Formula Tool 3: IF [course_count] = 1 then [course_count] ELSEIF [Row-1:Ties]>0 AND [Row-1:ACTIVE_DAYS] != [ACTIVE_DAYS] THEN [Row-1:Rank]+1+[Row-1:Ties] ELSEIF [Row-1:ACTIVE_DAYS]=[ACTIVE_DAYS] THEN [Row-1:Rank] ELSE [Row-1:Rank]+1 ENDIF

 

ChrisTX
14 - Magnetar

See this Weekly Challenge

 

Challenge #9: Analytics Ranking

For this challenge let’s look at ranking records when multiple records can have the same rank.

 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-9-Analytics-Ranking/td-p/36736

 

brianna_stirsman
5 - Atom

This was a great option, but what if my first ranking starts at 0.  I would like 0 to be the first rank of 1, at the moment its giving a 0 rank.

ChrisTX
14 - Magnetar

Add a formula tool
Change the value of the current field for Ranking:  Ranking + 1

 

If you haven't used the Formula tool...

 

Beginning users can find some great information on the Alteryx web site under Community > Academy.

 

Try Learning Paths
Try Interactive Lessons
Under the link for Videos, click the pinned post for Video Training Index, and look at the videos with Difficulty = Beginner
Under Weekly Challenge, click the pinned post for Weekly Challenge Index & Welcome, and try the challenges with Level of Difficulty = Beginner

 

Chris

 

SaoriYamguchi
8 - Asteroid

This solution is not use the multi-formula Tool, but I think this picture is hint.

 

https://www.lhit.co.jp/Alteryx/in_detail_multi-row_formula_tool/

 

hint.png

Labels