8 Replies Latest reply on Oct 4, 2016 6:48 AM by Henrietta Bathurst

# Including minus numbers in a Top N calculation

Hi all,

I'm trying to show movement across years (using trans dates) and have managed by using a Top N calculation to show the largest positive movements, however I would also like my table to show the largest negative movements (e.g. losses).

If anyone has any suggestions as to how to achieve this/whether or not it is possible it would be greatly appreciated!

Thanks in advance

• ###### 1. Re: Including minus numbers in a Top N calculation

Hi Henritta,

To get largest negative movements use this cal field:

( if index()<=10 then "top n" else if size()-index()< 10 then "bottom 10"

end.)

Here i showed you  top 10  and bottom 10 cal field, so that in one chart you will able to identify top and bottom easily.

Hope this will helpful to you.

Regards,

Triveni.

1 of 1 people found this helpful
• ###### 2. Re: Including minus numbers in a Top N calculation

HI Henrietta,

I believe you have used the Rank_Unique(Measure Name) for ranking,

Try the below

Rank_Unique(Measure Name, 'asc')

If you have used Set to find them, use the TOP dropdown box to change it to Bottom.

Still facing issues ? Please share your workbook in .twbx file (Sample data / Fake data is enough)

• ###### 3. Re: Including minus numbers in a Top N calculation

Hi,

Thank you so much for your speedy replies, unfortunately I don't think I quite explained myself properly, I don't want a separate Top N for top and bottom but rather a top N that discounts whether a number is positive or negative so that both are included in the same Top N, e.g. 1,000,000 and -900,000 are both seen as top numbers (if that makes any sense!)

• ###### 4. Re: Including minus numbers in a Top N calculation

Thanks triveni, calculation didn't seem to work for me though?

• ###### 5. Re: Including minus numbers in a Top N calculation

Ok you were asking like within top 10 all the negative and positive values should be included.

To get in that way follow the steps:

1. Create a set for top values

2. Create a set for  largest bottom values

3. combine both the sets to get those top values.

This will include both the positive and negative values.

Regards,

Triveni.

1 of 1 people found this helpful
• ###### 6. Re: Including minus numbers in a Top N calculation

Thanks for your help Triveni, have tried that and feel like I'm part of the way there, however my table is showing the positives and minuses as two separate fields (so 20 results show up for Top 10).

Not sure if this is asking too much of Tableau but would ideally like the positives and minuses to be processed together, so just looking at the size of the actual number not differentiating between positive and negative - e.g. -1,000,000 and 1,000,000 seen as both being in the top N.

Thanks again, sorry for asking such a complicated query!

• ###### 7. Re: Including minus numbers in a Top N calculation

Hi Henrietta - no worries about complexity, this seems fairly straightforward! You're trying to show 10 values, regardless of positive or negative, yes? You could show top 5 and bottom 5 to get your "10 outliers", but this doesn't take into account whether a negative value is "larger" than a given positive value. As such, you need a fairly standard mathematical operation: absolute value. Try something like this:
RANK_UNIQUE(ABS([Measure Name Here])) <= 10

1 of 1 people found this helpful
• ###### 8. Re: Including minus numbers in a Top N calculation

This sounds like exactly what I need thank you!

Where would you suggest actually putting it in the sheet?

Apologies to ask so many questions, very much a tableau newbie!

- Not to worry worked it out, thank you so much for all your help!