1 2 Previous Next 20 Replies Latest reply on May 21, 2018 5:35 AM by Monika Mucha

# How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

I have been using this formula but can't seem to rank the nulls RANK_UNIQUE(-RANK(SUM([Measure]))) Ben Neville

• ###### 1. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Would you provide a workbook to go off of? It may be as simple as a zn()

• ###### 2. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Thanks Galen for the fast reply.  ZN() makes the rank "0" but doesn't rank the rest of the zero's.  Below is an attached workbook example.

• ###### 3. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Seeing as you're using -RANK, I'm assuming you want them ordered descending, how do you want Tableau to logically determine how the 0's should be ranked? Your rank functions properly with the zn(), but when it hits the 0's it is going to say all 0's have the same rank. Am I understanding that correctly? Do you want the nulls to be ranked by another measure, or randomly numbered by discrete ranks?

1 of 1 people found this helpful
• ###### 4. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Galen,

I want to rank the nulls in descending order in continuation after the other ranks.

For example.

1 Chris - 100

2 Tom - 200

3 Peter - 300

4. Alec - Null

5. Jill - Null

• ###### 5. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Hey Chris - just threw in a ZN in what I think is the correct place. Does this look like what you're after?

1 of 1 people found this helpful
• ###### 6. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Ben Neville congrats on your move too!  forgot to tell you that!  Um, where should I put ZN in that formula?  thanks for the quick reply too.

• ###### 7. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

1 of 1 people found this helpful
• ###### 8. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4
3 of 3 people found this helpful
• ###### 9. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Thanks! If you edit the Rank calculation in the workbook I linked, you should find the "correct" place, Chris. Bora is correct - you could substitute INDEX() for RANK().

However, this is easier than you're making it. Just create a calculation called Rank that contains the calculation:
INDEX()
and place this to the far left on your Rows shelf. Next click the dropdown from the Employee pill, select Sort, Descending, and sort on your SUM([Cost]). Index is now simply a row counter, and the sort will handle the order of the employees. It's a single word calculation, and less overhead/computing.

1 of 1 people found this helpful
• ###### 10. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Thank you Ben Neville.  As always, you never fail to impress.  I see the workbook you attached Also thank you to Alexander Mou Bora Beran Galen Busch

• ###### 11. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

So here's my underlying problem.  I'm using a Top N filter (by Joe Mako) and trying to use a ranking system that reorders depending on the measure.  I have posted a workbook example.

The problem is the ranking keeps the same ranking instead of reshuffling it back to 1, 2, 3, 4.  This includes the null values that you have solved above in the previous workbook

Thanks,

- Chris

• ###### 12. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Here is the new solution:

https://public.tableau.com/views/TopNwithReshufflingofRank/Sheet2?:embed=y&:showTabs=y&:display_count=yes

1.Make [Rank] and [Top Measures Filter Parameter] use Rank_Unique(Sum(ZN(Measure)))

2.Make sure all table calc use Product

On Fri, May 8, 2015 at 6:49 AM, Chris Pham <

1 of 1 people found this helpful
• ###### 13. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Rank_Unique() is kind of the combination of rank()+index().

Index() is a very slippery function. In many cases, rank_unique() is a better way to rank and to index.

Vizible Difference: Nested Sorting and Top N per Category via Rank_Unique

Vizible Difference: The Nuance in Top N

1 of 1 people found this helpful
• ###### 14. Re: How do you rank items with have a value and also null?  For example, if I have values 100, 50, null, null -- I want to see 1, 2, 3, 4

Thank you Alexander Mou  The workbook is awesome and the additional explanation is great!

1 2 Previous Next