Rank based on agents by month

Hello all.

I am trying to get the ranking based on the following criteria

1) If the total is 1 for 5 of the 15 agents then they get Rank 1 and the remaining 6-15 agents gets rank 6( since first 5 agents have the total=1), this should be grouped by month the total rank number changes every month , I tried using the multirow formula with the

following expression:

if [Sum_Plan count]=![Row-1:Sum_Plan count]
then [Sum_Plan count]+1
else
[Row-1:Sum_Plan count]
endif

But it did not work. I have attached a sample data with the Rank column that is what i am trying to accomplish.

Any help will be greatly apricated. I am not able to share the original file as it has proprietary data, so i created a dummy excel file.

Thank you in Advance.

sample for rank1.xlsx

Topic of Interest

Accepted answers

Luke_C

Hi @mehu75

See below. I think for november the second rank should be 10 instead of 11.

I used the tile tool + multi-row formula to achieve this. Let me know if you have any questions.

New Workflow3.yxmd

atcodedog05

Hi @mehu75

Please find the updated workflow which meets the criteria.

Workflow:

Hope this helps : )

Rank (1).yxmd

All comments

Luke_C

Hi @mehu75

See below. I think for november the second rank should be 10 instead of 11.

I used the tile tool + multi-row formula to achieve this. Let me know if you have any questions.

New Workflow3.yxmd

atcodedog05

Hi @mehu75

@Luke_C has already nailed it. And agree with the point that it should 10 not 11. Just adding another method.

Workflow:

1. Using the first Multi-row formula tool to create Rank for each monthend by using groupby on monthend and adding 1 for each row.

2. Using the second first Multi-row formula tool to create general rank by checking if the previous row and current row total is different.

Hope this helps : )

Rank.yxmd

mehu75

Thank you both. I guess i did not realize another scenario that happened in my real world example is where if the entire month is 0 then the result shows up as 0. please see attached. I thank you both , truly appreciate it.

I will definitely marked it helpful .

Thank you

sample for rank1.xlsx

atcodedog05

Hi @mehu75

Please find the updated workflow which meets the criteria.

Workflow:

Hope this helps : )

Rank (1).yxmd

mehu75

Thank you

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167