Category Archives: Tip

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 UTC Conversion

In one of my earlier posts, I wrote about how to manually convert UTC time to Eastern time, adjusting for the Standard vs Daylight Savings. I’ve found this formula useful for some of SQL work but recently needed to incorporate it into Tableau.

So I decided to share the following calculated field. Simply cut and paste the formula below into your favorite text editor and Replace All the [date_field] with your date column name.

if date([date_field]) > #13-03-2011# and date([date_field]) < #06-11-2011# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2012# and date([date_field]) < #04-11-2012# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2013# and date([date_field]) < #03-11-2013# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2014# and date([date_field]) < #02-11-2014# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2015# and date([date_field]) < #01-11-2015# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2016# and date([date_field]) < #06-11-2016# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2017# and date([date_field]) < #05-11-2017# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2018# and date([date_field]) < #04-11-2018# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2019# and date([date_field]) < #03-11-2019# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2020# and date([date_field]) < #01-11-2020# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #14-03-2021# and date([date_field]) < #07-11-2021# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2022# and date([date_field]) < #06-11-2022# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2023# and date([date_field]) < #05-11-2023# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2024# and date([date_field]) < #03-11-2024# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2025# and date([date_field]) < #02-11-2025# then DATEADD(‘hour’,-4,[date_field])
else DATEADD(‘hour’,-5,[date_field]) end

Please note, this converts UTC to US Eastern time zone. So if you’re in another time zone, be sure to adjust the DATEADD values. I hope this helps.

Share Button

Time Duration in Tableau

Time conversion seems to be pretty popular topic for Tableau. I recently worked on a project where the users wanted to display media duration in hh:mm:ss format. I looked around the Tableau forum and saw others have had this issue too. As long as the duration does not exceed 24 hours, this formula works just fine DATETIME(SUM([total dur secs]/86400)). And then use Tableau Date formatting to change to hh:mm:ss. But if duration will exceed 24 hours, this formula will not work. In my case, duration will exceed 24 hours. So here’s how I tackled this problem.

First, I needed to calculate duration in seconds before I could create the required format. Working in media, time codes go down to the frames level. So first I converted frames into aggregated seconds based on the asset frame rate, [total dur secs] =  SUM([PROGRAM_DURATION]/29.97)

Now that my [total dur secs] field is in total seconds we can begin. There are probably many ways to perform this but this was the approach I took.

First, I created a [total hours] calculated field.

IF INT([total dur secs]/60/60) >=1 THEN
STR(IIF(INT[total dur secs]/60/60)> 9, “”, “0”)) + STR(INT[total dur secs]/60/60))
ELSE “”
END

The first line tests if the duration is greater than or equal to one hour. If that is true, then the second line tests if the duration is greater than 9 hours. If that is true, then create a blank, if false, then pad with a zero. Since I’m concatenating, the duration is converted to strings. If the total duration is not greater than an hour, then blank.

Next, I created a [total minutes : seconds] calculated field.

IF INT([total dur secs]/60) >=60 THEN
STR(IIF((INT([total dur secs]/60)-INT([total hours])*60)> 9, “”, “0”)) + STR(INT([total dur secs]/60)-INT([total hours])*60)
+ “:” +
STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))
ELSEIF INT([total dur secs]/60) >=1 THEN
STR(IIF(INT([total dur secs]/60)> 9, “”, “0”)) + STR(INT([total dur secs]/60))
+ “:” +
STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))
ELSE “00:” + STR(IIF(ROUND([total dur secs])> 9, “”, “0”)) + STR(ROUND([total dur secs]))
END

Let’s break down what is happening in the calculated field. The following portion below checks if [total dur secs] is greater than or equal to 60 minutes. Because I want time formatted as hh:mm:ss and I don’t want the minutes portion to exceed 60. So I subtract out [total hours] as minutes. Again, using the IIF to pad the extra zero as needed.

IF INT([total dur secs]/60) >=60 THEN
STR(IIF((INT([total dur secs]/60)-INT([total hours])*60)> 9, “”, “0”)) + STR(INT([total dur secs]/60)-INT([total hours])*60)

The next section below is calculating the seconds portion of the duration.

STR(IIF(ROUND([total dur secs]INT([total dur secs]/60)*60)> 9, “”, “0”)) +
STR(ROUND([total dur secs]INT([total dur secs]/60)*60))

I have to round [total dur secs] because I don’t want decimals. Then similar to the minutes calculation, I want to subtract out minutes that exceed 60. The IIF again pads for zeros as necessary. 

The ELSEIF and ELSE basically repeat the same calculation, checking if minutes are greater or equal to 1 or zero respectively.

Lastly, I bring [total hours] and [total minutes : seconds] together in a new calculated field, [Total Duration].

[total hours]+ IIF([total hours]=””,[total minutes : seconds],”:”+ [total minutes : seconds])

So just as I’m about to finish this set of calculations, a new message appears in my inbox. I subscribe to Tableau Zen Master – Jonathan Drummey’s blog, and he just posted a much simpler version of time duration on, Drawing with Numbers. I guess I just prefer the scenic route. Check it out his work though, it is great.

How have you handled time duration formatting in Tableau?

 

Share Button

Tableau Server Tip

Tip time again. And this one focuses on Tableau Server. One of my favorite features of Tableau Server is the ability to publish data sources. I work for a global company and we constantly share data with teams in international offices. This data can range from SQL Server/Oracle data to Excel files stored on a multitude of share drives. Publishing data sources allows our global teams to all access data needed for a given project or report, without needing access to where the underlying data is stored. This has been advantage and allowed collaboration between regions, all while working with a single version of the truth.

Ok, now getting to the tip. Let’s say you’re given a workbook that already contains charts, tables, dashboards, and of course, calculated fields. The workbook is connected to a published data source on your server. You try to edit a calculated field and the only option is Edit Copy, which makes a new calculated field.

ScreenHunter_02 Jul. 24 16.45

This is not ideal because the calculated field you’d like to edit is already embedded in several locations of the workbook. So how do you edit the original field?

Easy, simply right click on the data source name and select Create Local Copy.

ScreenHunter_03 Jul. 24 16.48

Save the data source to your desired folder. You’ll now have a new data source.

ScreenHunter_04 Jul. 24 16.50

Next, swap data sources so the exists workbook sources from the local copy.

Select Data > Replace Data Source…

ScreenHunter_01 Jul. 24 19.59

Now when you right click to edit a calculated field, you’ll be able to edit the original field that is already being used throughout the workbook.

ScreenHunter_05 Jul. 24 16.51

From here, you can proceed with the needed task. Just remember that if you republish the data source, your calculated field edits will be available for others. So depending on your ultimate goal, know if you should overwrite the original published data source or not.

Thanks! And happy Tableau’ing!

Share Button

Tableau Server Tip

As an administrator of Tableau Server, you’re granted access to six dashboards that show a variety of server activity/performance/utilization information. These dashboards are connected to Tableau’s internal PostgreSQL database. What if you’d like other server users to access these admin dashboards but you do not want to grant those users Administrative rights? There are four easy steps to accomplish this.

  1. Create a PostgreSQL password
  2. Find and copy the tabbed_admin_views workbook
  3. Open workbook and update database connections with your server and password
  4. Publish workbook

Step 1:

  • Log onto your machine that runs Tableau Server and in Command Line navigate to the bin folder
  • Type tabadmin db pass [password] (in version 9, “dbpass” is one word)
  • For [password] input your desired password
  • Type tabadmin configure
  • Type tabadmin restart

You’ve now set a password to the internal PostgreSQL database. You’re free to connect to it from a new workbook and poke around. Note, if you have not done so, you’ll need to install PostgreSQL database drivers.

Step 2:

Thank you Adolph Barclift, DC-VA Tableau User Group Co-Chair for the assistance on this step. 

While logged in to your machine running Tableau Server, open folder …Tableau\Tableau Server\[version]\wgserver\z5\WEB-INF\admin

Make a copy of the tabbed_admin_views.twb and save to desired location.

ScreenHunter_01 Feb. 08 12.44

Step 3:

Open the workbook and click OK when prompted to log in, you’ll get an error and click Yes to edit the connection.

Complete the connection dialog with your credentials. Port, Database and Username should remain unchanged.

ScreenHunter_02 Feb. 08 13.03

This workbook has eight separate connections so you’ll have to repeat the process seven more times.

Step 4:

Publish workbook, embedding credentials, and set your security as desire.

Congratulations! You now have your own administrative dashboards. What’s even better is that you can create custom admin dashboards! Check out Russell Christopher’s series on Tableau History Tables for more details on PostgreSQL schema.

Share Button

Year over Year Quick Tip

Over the years as a Tableau user I’ve learned to adjust my vizzes depending on the audience. Displaying Year over Year is an important metric to many at my organization. Here is a quick and easy way that I use often.

I’m using the Superstore sample data that ships with Tableau. Place Order Date in the Columns shelf and Sales in the Rows shelf, change the Marks to Bar.

ScreenHunter_06 Feb. 01 20.01

Now grab Sales again and place it in the Label Marks. Hover over the right edge of the pill until you see the triangle. Click it and move down to Quick Table Calculation > Percent Difference

ScreenHunter_09 Feb. 01 20.05

And that’s it! Tableau will use the earliest date as the base line and calculate the YoY change.

ScreenHunter_10 Feb. 01 20.06

I like this view because the viz utilizes the Y axis to display the Sales in dollars, but the label shows the YoY growth. The two metrics combined provide more context to your audience.

One thing to keep in mind is that if current year is not over, filter for months. Let’s say today is currently June 2013 and management wants to know how Sales are tracking against previous years.

ScreenHunter_12 Feb. 01 20.09

Hold right click as you drag Order Date into the Filter shelf. Filter on Months and only include January to May. Very simple!

Share Button