7 Replies Latest reply on Oct 30, 2018 6:10 AM by Ombir Rathee

# Limit number of records in each rank

Hi,

I am new to tableau and I have a unique requirement that I am trying to solve. Suppose I have list of students and their marks, can I limit the number of records in each rank when I rank them by marks, Eg, suppose 8 students scored 95 and 5 scored 94 and If I use the rank command then 8 students will have rank 1 and 5 students have rank as 2. I have a limitation that not more than 6 students should be in a single rank. So my expected output is 6 students will have rank 1 and remaining 2 will have rank 2 followed by 5 students in rank 3. There no criteria to choose which six students will be in rank 1 and which 2 will be in rank 2; i.e. it can simply be the order in which the calculations are done. Is there a way to achieve this using any function in tableau?

Thanks for the help,.

• ###### 1. Re: Limit number of records in each rank

Like this ?

I Created three calculated fields:

One:

IF [Agg Marks]=LOOKUP([Agg Marks],-1) THEN

PREVIOUS_VALUE(1)+1

ELSE

1

END

Two:

IF [One]=1 OR LOOKUP([One],-1)%6=0 THEN

1

ELSE

0

END

Modified Rank:

RUNNING_SUM([Two])

• ###### 2. Re: Limit number of records in each rank

I took me a while to figure out the solution of this problem but my gut is saying there is still a better solution available. What would be your approach to solve this. Just curious to know.

• ###### 3. Re: Limit number of records in each rank

Not saying there isn't a better solution, but I can't think of one. Your one actually looks pretty elegant to me (like the use of the mod 6!)

With Ranking (and this goes for any other data technology I've ever come across)...You can either rank unique or allow equal ranks (and then determine if any elements after any equals, start from rank + 1, or the number of equal ranks +1). So in order to get this very niche use, where the first 6 can be equal, the only way I can think is with the self-referencing PREVIOUS_VALUE.

Good work!

1 of 1 people found this helpful
• ###### 4. Re: Limit number of records in each rank

Thanks for the reply, Ombir. It is working fine in your file, but when I try it in the attached file I am unable to achieve the same.My marks is mapping of grades to numbers. I feel the issue is because I am unable to sort the records based on grade values. Can you please help me correct this?

• ###### 5. Re: Limit number of records in each rank

Like this ?

1 of 1 people found this helpful
• ###### 6. Re: Limit number of records in each rank

Yes, Thanks for your help. Can you please point me towards best way to learn Tableau?

• ###### 7. Re: Limit number of records in each rank

First clear your fundamentals. Tableau Desktop documentation is the best place to learn. See below link.

https://onlinehelp.tableau.com/current/pro/desktop/en-us/default.htm

Once your fundamentals are clear then community is the best place to enhance your skills. Try to solve people problems on forum. Invest 1-2 hour daily on Tableau community. Side by side participate in Makeover Monday, Tableau Tip Tuesday, Workout Wednesday series. Reverse engineer Tableau workbooks that Tableau masters publish on Tableau Public. You'll become expert if you will follow this routine.