8 Replies Latest reply on May 1, 2015 9:01 AM by kettan

# Table Calculation - Calculate Across, Rank Down

Is it possible to rank years <table down> for a running total per year <table across>?

What I want is to rank years into 5 pots for each month: { Best | Upper | Median | Lower | Worst }

The purpose is to quickly see how we are doing year-to-date in comparison with previous years.

See more in attached workbook.

• ###### 1. Re: Table Calculation - Calculate Across, Rank Down

Is duplicating the source out of the question? Then you could use this source as the total lookups without having to do a table calculation.

1 of 1 people found this helpful
• ###### 2. Re: Table Calculation - Calculate Across, Rank Down

It is worthy to try. I do not know how to do this lookup calculation, and hope to get some help to get started.

I have duplicated the source in attached spreadsheet and made a worksheet ready for coloring.

PS! I use only one source in my production which also is relative small (117,000 rows). The dashboard takes 4 seconds to update and filter. It has 2 graphs, 2 highlight tables, 1 table, 4 data filters, and 3 parameter filters.

• ###### 3. Re: Table Calculation - Calculate Across, Rank Down

Here's a solution that gets you most of the way. The only thing that isn't quite right is that there are sometimes 3 or more values classed as median as they were so close. You would need to work out a way to find the two values that made the median. If there were an odd number of years, this would not be an issue.

• ###### 4. Re: Table Calculation - Calculate Across, Rank Down

Thank you very much, Alex. This is more or less the correct answer. I will try to work on the median myself.

• ###### 5. Re: Table Calculation - Calculate Across, Rank Down

It's actually really tricky I think - to know if it was part of the median, you need to know the next value along, but you can't lookup that value because it needs to be ordered by another table calculation  - running total across, then down. If you resorted to mid percentile, perhaps that would work.

• ###### 6. Re: Table Calculation - Calculate Across, Rank Down

You're right, it is very tricky, too tricky for me

About to give up, the thought suddenly hit me that it is acceptable to be without median for equal number of years, although it would be better with 2x median. This made it fairly simple to write the formula. See more in attached workbook.

My production workbook currently shows year 2004-2012 and will therefore show the median! For next year I hope Tableau 8 can handle this calculation

Thank you very much for your help!

• ###### 7. Re: Table Calculation - Calculate Across, Rank Down

@kettan, someone pointed me to an old thread where you'd mentioned this workbook and I answered it there: Re: Dynamic table partitioning

• ###### 8. Re: Table Calculation - Calculate Across, Rank Down

I am not sure if I should have answered  Re: Dynamic table partitioning  since I don't need an answer and that it probably was addressed to a third person or thread which had pointed you to it.

Just so you know, I have no need of an answer because I don't work in the company anymore where that color scheme was used. Technically I would of course be interested, but don't want you to feel that you should answer it.