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

[SaoriYamguchi]

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

Name	Score	Rank
Mike	100	1
Sarry	200	2
Emi	200	2
Koji	300	4

How can I make it?

[BenMoss · Accepted solution]

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...

Ben

[SaoriYamguchi]

Thank you！

I got it!!!!!!

[ThizViz]

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

Very helpful for those of us searching for tutorials!

[DHB]

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;

GROUP	ACTIVE_DAYS	Ties	Rank	What the Rank Should Be
A	2	0	1	1
A	2	1	1	1
A	5	0	3	3
A	5	1	3	3
A	6	0	5	5
A	6	1	5	5
A	7	0	7	7
A	7	1	7	7
A	7	2	7	7
A	8	0	10	10
A	8	1	10	10
A	9	0	12	12
A	10	0	13	13
A	10	1	13	13
A	11	0	15	15
A	15	0	16	16
A	15	1	16	16
A	18	0	18	18
A	18	1	18	18
A	23	0	20	20
A	32	0	21	21
B	0	1	0	1
B	0	2	0	1
B	0	3	0	1
B	0	4	0	1
B	0	5	0	1
B	0	6	0	1
B	0	7	0	1
B	0	8	0	1
B	1	0	9	9
B	1	1	9	9
B	2	0	11	11
B	2	1	11	11
B	3	0	13	13
B	3	1	13	13
B	5	0	15	15
B	6	0	16	16
B	6	1	16	16
B	8	0	18	18
B	8	1	18	18
B	8	2	18	18

[DHB]

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]

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]

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]

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]

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/