Rank based on agents by month

[mehu75]

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.

[Luke_C · Accepted solution]

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.

[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 : )

[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 

[atcodedog05 · Accepted solution]

Hi @mehu75 

Please find the updated workflow which meets the criteria.

Workflow:

Hope this helps : )

[mehu75]

Thank you