Monthly Archives: May 2015

Tableau in the office – episode 3

I’ve been revising some old dashboards at work that definitely are due for a refresh. One of the updates is to demonstrate Year over Year for a given metric. I wanted the calculation for current year to compare Jan 1 to date against previous year. But if the user selects to view YoY of previous years, I wanted to show full year difference, not Jan 1 to date. Below was my approach.

For sample data, I created an Excel file with random values per date from 1/1/2011 to 5/31/2015. You can download a copy of it here. To get my desired result, it will take one parameter and three calculated fields in the Filter shelf.

First, I created a Parameter for Year.

ScreenHunter_01 May. 20 16.07

Then I created a formula to only show the year selected in the Parameter and the year before it. This is a boolean expression and I place it on the Filter shelf, selecting the True result.

[Year] = year([Date]) or [Year] – 1 = year([Date])

In my scenario, I only want to show YoY of a year at a time. And since YoY is created with a table calculation for percent difference from previous, I needed a way to keep the result but hide the previous year in the view since it is blank. I used a simple index() calculation and placed this in the Filter shelf, selecting the value “2”.

Next, the following formula creates a calculated field which again is used in the Filter shelf. I’ve broken down the formula with the //comments.

//If a data point’s date is this year and in past months, keep it.
if [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) < datepart(‘month’, today())
then ‘keep’
//if a data point’s date is in this year and in current or future months, filter it.
elseif [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) > datepart(‘month’, today())
then ‘filter’
//if a data point’s date is in past years, keep it.
elseif [Year] < datepart(‘year’, today())
then ‘keep’
else ‘filter’

Place this calculated field in Filter shelf and select “keep”. Here are the three calculated fields in the Filter shelf. Any field I create strictly for filtering, I name with a “f.” prefix so I know its intended purpose.

ScreenHunter_02 May. 21 09.57

Now I place the Units measure in the Text shelf, and change the Sum(Units) to a Percent Difference table calculation.

ScreenHunter_03 May. 21 10.02

I also created a “label” field so the text changes depending on the Year parameter. This is to make it clear to the audience that YoY for current year is a Jan 1 to date comparison over previous year. But previous year to previous year minus 1 comparisons are Full Year over Full Year.

if [Year] = datepart(‘year’,today()) then “YoY” else “FYoFY” END

Add this “label” field to the Text shelf and lastly, add Year(Date) to the Text shelf too. Once all my text fields are in place, I edit the label as follows:

ScreenHunter_06 May. 21 10.09

The final result is below. Change the Parameter to view the differing YoY or FYoFY calculations. I included a table with the sample data.

Thanks for tuning in!

Share Button

Tableau server tip – Email Action

A quick and easy way to provide your user base the ability to email any questions about a given dashboard is to use a URL action. If you’re unfamiliar with actions, definitely check out this Knowledge Base article.

First, I decided to use an image of a question mark as a flag for the user. After a quick google image search, I found the one below that I liked. Download the image and save it to your …\My Tableau Repository\Shapes folder. I created a “Custom” sub-folder in this path to keep my custom shapes together.

ScreenHunter_01 Mar. 31 14.38

In your workbook, create a new sheet. The data source used is unimportant as you’re just going to create a viz with this shape in it.

Change the Marks to Shape, click the Shape pill, click More Shapes and choose the Custom folder (or what ever you named it) from the Select Shape Palette drop down. Click your shape and click OK.

ScreenHunter_02 May. 19 16.19

Now drop the omnipresent Number of Records measure into the Shape pill. You should now have a sheet that looks like this:

ScreenHunter_03 May. 19 16.23

You can enlarge the view by dragging along the sides of the sheet. Use the Size pill to make the shape larger or smaller as desired. I also format the sheet to remove Row and Column Dividers. In the Tooltip, replace existing text with something like “If you have questions about this data, please email:”.

Place this sheet anywhere in your dashboard. I prefer mine at the upper corner, right of the dashboard title.

ScreenHunter_04 May. 19 16.30

Create a URL Action. Name it your desired receiving email address. In the URL section, type: “mailto: email_address”

ScreenHunter_06 May. 19 16.36

Now when a user hovers over the image, the Tooltip will appear with the text and hyperlink. In my organization we use MS Outlook. Clicking the link launches a new email which contains your receiving email address already populated.

ScreenHunter_07 May. 19 16.40

Pretty simple trick. Hope you enjoyed it.


Share Button

Tableau Server Tip – Tabcmd

It’s been a little while since I have been able to post. But here I am back for a quick tip. I’m embarrassed to say that I only recently learned that Tabcmd (I pronounce it “Tab Command”) utilities can be installed and used from other machines, not just the one running your Tableau Server installation. If you’re unfamiliar with Tabcmd, these are a set of command line utilities that allow for automation and batch processing of tasks. This can be very handy when compiled into a BAT file and scheduled as a task.

Or just fun to play with, but be careful. There are delete commands that can be used. For the full list of Tabcmd options, check out the following link.

For you Alteryx users, Tabcmd can be implemented into your module. Interworks published a great article on how to take an output of a TDE and publish it to Tableau Server with Tabcmd. This is where being able to install Tabcmd on your machine shines. You don’t need to have Alteryx installed on your machine running Tableau Server. Check it out here.

Thanks for tuning in.

Share Button