I’m working on a project that requires me to calculate the distance between stores in order to plan resource allocation.  Pretty cool stuff that could have a huge impact if it pans out.
Naturally I want to do this in Tableau, but I since I hadn’t done this before I turned to the Tableau Forum and found this great step-by-step tutorial. 

TIP: For those of you that may be new to Tableau, I would highly recommend that you use the forum if you’re approaching something you’ve never done before.  You’ll often find that someone has already done something similar and it’ll save you a lot of time versus re-inventing the work yourself.

Tableau has outlined this as a 22 step process, but they go through it in extreme detail.  Note that your data source MUST have latitude and longitude available in this example.  Here’s a slimmed down version for you (some of this is taken directly from the article):

1. Connect to your data source, select Single Table, then select Custom SQL
2. Create an inner join on a second instance of the table where the locations from the two instances are not equal (refer to the SQL script in the detailed instructions)
3. Click OK, then Extract the data.  For me, I’m looking at 7271 stores, so the self join will result in about 50M records.  Leverage the power of Tableau’s data extracts!
4. Double-click your latitude and longitude fields to start building the map.  You may need to set the geographic role of the fields if you don’t have them named Latitude and Longitude.
5. Use the Great Circle Distance formula by creating a calculated field named Distance (or the name of your choice)
   
   The formula is:
   3959 * ACOS
(
SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) +
COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long]))
)

NOTE: To calculate miles, use 3959 as the first number.  For kilometers, use 6371 as the first number (thanks to Shawn Wallwork for the comment)

 
6. On the Marks card, in the list, select Line. This will create lines between all locations on the map.  Start with only a couple locations if you have a huge dataset, otherwise it could take some time to draw all of the lines.

From this point, you can perform tons of different analysis.  One example would be to drag Distance onto the Color shelf and the Label shelf on the Marks card to color code and label the distances between each point on the map. 

Think about how you could blend other data source.  For me, I might have home zip codes for employees in another data source and I want to see all stores within a certain radius of each employee.  The possibilities are almost endless! 

You can find a sample workbook for how all of this is done here.  I know I’ll be using this technique over and over again.

There a good chance you’ve run into this scenario before, maybe in a past life in Excel or with Tableau:  You have a chart with a bunch of bars or columns or maybe a line chart, and you want the top 5 values labeled.  Ideally, the chart would look something like this:

With Tableau, you can manually assign labels to these points, but wouldn’t it be better for the points that are labeled to change dynamically based on the data you have selected?  There’s no easy way to do this in Tableau, but as always, there’s a workaround that’s quite simple once you implement it once or twice.

Be patient as you read your way through this; it might seem a bit complicated, but I’ll detail every step.  Let’s get started.

1. Drag the Order Date dimension onto the Columns shelf, right-click on the pill and choose All Values (This changes the Order Date field from a Discrete dimension to Continuous; not a critical step, simply personal preference)

2. Drag the Sales measure onto the Rows shelf (I’ve filtered the Order Date to 2010 only, but that’s not necessary either)

 

3. Create a parameter, I named mine Top X, with the following properties:

NOTE: I could have chosen to always label the top 5 or top 10 values, but I want the consumers of the dashboard to be able to select the number of values they want to see labeled, thus the need for a parameter.

4. Right-click on the “Top X” parameter and choose “Create Calculated Field…”  Name the field “Top X Label” and enter this formula:

IF INDEX()<=[Top X] THEN SUM([Sales]) END

I’ll explain the need to use the INDEX function in a bit.

5. Right-click on the “Top X” parameter and choose “Show Parameter Control”

6. Drag the Top X Label calculated field (from step 4) onto the Label shelf on the Marks card.

7. Here comes the trick: Right-click on the Top X Label measure and choose “Edit Table Calculation”

8. In the Table Calculation dialog box, change the Compute Using option to Advanced

9. In the Advanced window, change the Order Along settings to the Sum of Sales Descending.  This will force the Top X Label field to index the values based on Sales from highest to lowest (thus the reason the calculated field compares to the INDEX() function).

That’s it!  The top 5 points are now labeled. You’re chart should look like this:

You can use the Top X parameter to pick the number of values you want to label.  Even if you filter the data, maybe to only show the East Region, the labels will still work properly.

But this is Tableau, so let’s take it a step farther.  Maybe you need to label the top 5 and the bottom 5.  There’s a neat little way to do this too.

1. Duplicate the Top X parameter and rename it Bottom Y and the show the Bottom Y parameter control

2. Duplicate the Top X Label calculated field, update it to reference the Bottom Y parameter and rename it to Bottom Y label:

IF INDEX()<=[Bottom Y] THEN SUM([Sales]) END

So now what?  There’s no way to add a second label!  True, but there IS a way to add a secondary axis.

3. Drag Sales onto the Rows shelf, right-click on it and choose Dual Axis

4. Remove “Measure Names” from the color shelf (we don’t need different colors since we’re using the same measure twice)

5. On the Marks card, click on the carrot on the upper-right of the card and choose Multiple Mark Types

6. Click the right arrow twice until you see “SUM(Sales) (2)”, then drag the Bottom Y Labels calculated field onto the Label shelf

7. Right-click on the Bottom Y Labels field, choose Edit Table Calculation, then repeat steps 8 & 9 above.  The only difference is that the advanced table calculation should be in ascending order for the Bottom Y Labels:

You’re done!  You now have an interactive chart that allows the user to pick the number of top and bottom values they want to see.  Interact with it, download it and see how it works for yourself.

Finally, I would like to thank Joe Mako for his help in walking through this situation.  He helped me with the formula and advanced table calculation for the Top X Label field.