Notice how the font for "Office Supplies" next to the dropdown selector (9 pt) does not match the font size of the option displayed (12 pt). In this video, I show you how to work around that in a couple different methods.

The final product uses set actions. This method also applies to Parameters, but I have not included those in the video. The steps are mostly the same except you would use a Parameter Action instead of a Set Action.

Enjoy!

Be sure to check all of the requirements on Emma's blog. I immediately knew how to do all of this, so it was a matter of getting it done. The trickiest bit was the year over year change in the map. Emma and I approached the calculations required differently, as we always seem to do. She chose to use basic expressions while I used LOD expressions. Her way is definitely simpler!

Click on the image for the interactive version. Be sure to post your version to twitter and tag @EmmaWhyte and @VizWizBI so we can see your work.

This week I go back to a post I wrote in January that showed how to create a parameter that returns a value, but those values have multiple number formats (e.g., pounds and percentages). I then show how to use custom number formatting to display the metric selected with the proper number format.

Note: This logic only works if all of the numbers you are showing are positive.

Download the workbook here.

In this example, I've created a simple dashboard with three viz types: Line Chart, Map, and Bar Chart. I created three separate sheets for each type of chart, one for each metric: Sales, Profit Ratio, and Order Quantity, each having a different format for a total of nine worksheets.

Step 1 - Create the lines charts. I started with Sales and then duplicated the sheet and replaced Sales with Profit Ratio and Order Quantity, leaving me with three separate worksheets.


Step 2 - Create a map for each metric. Again, I end up with one worksheet for each metric.


Step 3 - Create a bar chart for each metric, giving us three more worksheets for a total of nine.


Step 4 - Create a parameter with a list of the metrics.


Step 5 - Create a calculated field to get the value selected in the parameter created in Step 4.


Step 6 - Show the parameter control on one of the Sales worksheets and choose Sales from the list.


Step 7 - For each of the worksheets that is using the Sales measure, drag the calculated field created in step 5 onto the Filters shelf and explicitly choose Sales from the list. There should only be one item in the list of available selections. Do NOT choose the "Use All" option or this hack won't work.


Note what happens when you change the selection in the parameter (e.g., choose Profit Ratio); the worksheet will go blank. This is exactly what we want because we want the Sales worksheets to go blank when a different metric is chosen.  Go ahead, give it a try.

Step 8 - Change the metric chosen in the parameter to Profit Ratio and then add the calculated field created in step 5 onto the Filters shelf for each of the worksheets that contains Profit Ratio and explicitly choose Profit Ratio from the list.


Again, notice how Profit Ratio is our only available selection in the filter list.

Step 9 - Repeat step 8, but this time choose Order Quantity and add the filter to each of the Order Quantity sheets.

Step 10 - Create a new dashboard.

Step 11 (optional) - Add the title to the dashboard and in the title, include the parameter. This way the title will make the dashboard more intuitive.


Step 12 - Add two horizontal containers to the dashboard, one above the other.

Step 13 - Add the three line charts to the top container...


...then hide the titles for each worksheet.


Step 14 - Add all of the maps and bar charts to the bottom container...


...then hide the titles for each worksheet.


Step 15 - A bit of cleanup, like sizing the charts to fit the view, removing the color legends, making it all looks pretty and floating the parameter and you're all done!


Go back to the viz at the top to play with the interactive version. Here's a video recording of how I built this dashboard.

The feedback was so positive from my viz of the sleeping habits of geniuses that I had to give it another go. This time, I take a deeper look at the 2014 class of Tableau Zen Masters. Surely this group meets John Medina's requirement of 8 hours of sleep, right? Absolutely not! In fact, only 40% of them get more than 7 hours of sleep.

I blame Tableau. Clearly the Zens spend too much time playing with Tableau and helping people solve the hardest Tableau problems. Judge for yourself with the viz below.

• Hover over a Zen and a summary of their stats will appear on the right.
• Hover over a Zen for links to their Twitter profile and website.
• Rank the Zens using the parameter on the right: By amount of sleep, when they go to sleep, when they wake up in the morning or look at a simple alphabetical list.
• Use the minimum sleep parameter to determine how much you think a Zen needs to sleep and watch the viz update.

Download the data here and the workbook here.

Reader Brian Bieber (no relation to Justin), a Data Analyst at Vanguard, sent me this question:

I'm currently running some monthly data where a list of records is assigned a KPI indicator like G/Y/R. I've been asked to produce a follow-up piece that would show "movers" from the prior month's data, just in a simple text view. So I guess what I need to try and do is figure out how to do a lookback/compare from prior month data to see who went from Y/R to G on the positive change side and who went from G to Y/R on the negative side.

What Brian didn't realize was that answer to his question was pretty much in the question itself; he needs to use to LOOKUP() function. I sent Brian a solution, but decided to fancy it up a bit more and add some more functionality using parameters:

The steps for building a KPI movers viz like this are pretty straight forward. I'm using the Superstore Sales data set that comes with Tableau in this example.

Step 1: On a PC, right-click drag Order Date to the Columns shelf.  On a Mac, Option+Drag Order Date. Choose the continuous month option.


Step 2: Right-click on the Order Date pill on the Columns shelf and change it to Discrete.


Step 3: Right-click on the Order Date field on the Column shelf and choose Show Missing Values. This is important because it turns on data densification. Months where data does not exist get displayed.

Step 4: Add the dimensions you would like to slice the data by to the Rows shelf. For this example, I've used Region and Order Priority.


Step 5: Create a calculated field called Last and change the Default Table Calculation to Order Date.



Step 6: Right-click on the Last field you just create in the Measures area of the Data window and choose Convert to Discrete.

Step 7: Drag the Last field to the Filters shelf and choose 0. This will change the view to only show the latest date.


Step 8: Create a calculated field to get the value for the most recent month. Notice that I'm using the LOOKUP function. Change the Default Table Calculation to Order Date.


Step 9: Create a calculated field to get the value for the previous month. The only difference here is we need to offset the LOOKUP by 1. Change the Default Table Calculation to Order Date.


Step 10: Create two parameters: one to determine the lower limit of the top KPI (Great Performers) and one to determine the lower limit of the middle KPI (Average Performers).



Step 11: Create the KPI calculations for the most recent month and the previous month. Notice that I'm using the parameters.



Step 12: It's up to you if you want to follow this step. For this specific example, I wanted to see the KPI for this month and last month in the table, so I've added them to the Rows shelf.


Step 13: Create a calculated field that returns a boolean to compare the values for this month to determine if they are a "Mover". I'm using Brian's definition for this example.


Step 14: Drag the Movers field to the Color shelf. I chose to set the colors to a light gray when True and red when False. This way only the Movers stand out.


Step 15: Optional - Change the Marks type to Shape and drag Movers to the Shape shelf as well, then pick shapes that give the affect your looking for. I chose diamonds for the Movers and circles for everything else.


Lastly I did a few cleanup items before placing the worksheet on the dashboard you see at the top of this post:

1. Moved Order Date to the Detail shelf
2. Reduced the level of the row divider
3. Removed the bold text from Region
4. Added a calculated field to the Filters shelf that checks that the value in the Great Performers parameter is larger than the value in the Average Performers parameter.

This method could easily be changed to accommodate many different scenarios like day over day, week over week or year over year.

Download the sample workbook here.

As I was preparing for my trip to Israel, the team posed an excellent question: Why doesn’t the date slider quick filter automatically show the latest date when the data is refreshed on Server?
I saw on this thread that it’s still an unsolved problem.  In a nutshell, here’s a summary of the problem:

1. You created a dashboard that has a date slider quick filter and you’ve told Tableau to use all dates.
2. The next day when the data source refreshes, the quick filter doesn’t automatically include the new date(s).  The slider has to be manually moved to the right to accommodate for the new data.

I didn’t have an immediate solution, so I did what I always do when I’m stuck…I asked for help!  That’s right, Zen Masters don’t know everything.  In fact, I don’t know anywhere near everything about Tableau.  I contacted my good friend Joe Mako and he graciously hopped on a join.me with me.  What I truly love about Joe is that he’s incredibly patient and wants to make sure that we work through problems TOGETHER.  Sure, he could have simply sent me a solution, but he wanted to make sure I understood completely.

Let me first explain the problem in a bit more detail.  Assume that you have a set of sales data for the first few days of the year.



And you built a cool dashboard with a date slider like this:



You publish the dashboard to Tableau Server and set the data source to refresh each night.  But when you look at it a couple of days later, you notice that the chart hasn’t changed.



You can clearly see that four more days should be included, but the slider is still stopped at January 10th.  Tableau does not automatically adjust the date slider; it stays at whatever dates were picked when you published the workbook.

That leaves you with two options, both of which are not satisfactory:

1. Republish the workbook every day.
2. Tell your users that they have to adjust it themselves every day.

Or maybe there is a workaround.  This is where Joe stepped in.

Step 1: Create a parameter that allows two options and show the parameter control.



Note from Joe: This allows us to default the view to including all dates, while enabling the interactor to decided to apply a date filter.

Step 2: Duplicate the data source (or alternatively create a data source that has only the dates since that’s all you need to make this work, or you could use custom SQL like “SELECT DISTINCT Date FROM Table”) 

Note from Joe: This will give us a data source with a valid date range, a kind of dynamically populated parameter.

Step 3: Unlink the Date field in the secondary data source.  Ignore any warnings. 



Note from Joe: This way there is no Group By clause sent in the query to the secondary date list data source.

Step 4: Create a calculated field that checks for matching dates.



Step 5: Replace the Date filter on the worksheet from the primary data source with the Date field from the secondary data source and show the quick filter.  Again, ignore any warnings. 



Note from Joe: The combination of this calculated field in the primary data source, and a filter on the Date field directly from the secondary data source, is where the magic happens. The filter on Date that we just placed will happen in the query to the secondary data source, then the aggregations to get the MIN and MAX Date in the secondary, and then the comparisons that happening the formula will happen after the results are returned from each data source. This is effectively enabling us to get the selection of the filter available to use in the calculated field.

Step 6: Add the date match calculated field that you created in Step 4 to the Filters shelf and set it to At Least 1. 



Note from Joe: The INT() in the formula turns the Boolean value into an integer because a Boolean cannot be Continuous and Tableau cannot filter on a Discrete Measure if it is not a table calculation.  So by changing the data type, we can make the pill a Continuous Measure and filter on it.

Step 7: Clean up the dashboard – Remove the quick filter that is showing, add the quick filter for the Date slider and show the parameter control.

When you publish the dashboard, be sure to pick All Dates from the parameter created in Step 1 above.  The Date slider will still not move, but at least we are now defaulting the dashboard to always show all dates and then the user can filter if they wish.

You can download the sample workbook here.