Archive

Archive for the ‘Tableau’ Category

Create fixed bins from a measure in Tableau

June 18th, 2017 No comments

What feels like a long time ago, pre-Tableau 9, I wrote an article on how to create fixed bins from a measure in Tableau. At the time it was complicated, data duplication and blending was required. Then Tableau 9 came along and this suddenly creating bins from a measure became very easy.

The game changer was the FIXED Level Of Detail function.

The FIXED function now means a simple formula replaces a complex data duplication / data blend. Read more…

Categories: Tableau Tags:

Tableau dashboard navigation without the double click

May 14th, 2017 No comments

There are use cases where using buttons can aid Tableau dashboard navigation. For example in your Tableau report you may have a summary dashboard and a detailed drill down. Perhaps you want the user to drill down from the summary to the detail dashboard on a click.

Tableau Actions make this drill down very simple. It’s the drilling back up that is more complicated. In this example I have created a very simple bar chart of sales by Country using Tableau’s sample superstore file. I have also created a a table containing sales by State and Year. This table is the drill down for this example.

To keep things simple the two worksheets were used in two dashboards. The Summary dashboard contains only the bar chart worksheet, the Detail dashboard is only the table.

To drill down from the Summary dashboard is simple using Actions. Click Dashboard – Actions and set a new filter action using Summary as the Source Sheet, run action on Select and the Target Sheet is the Detail. Target Filters are All Fields.

Filter Action Summary

That is the drill down new set up. Click a Country name and it’ll take you to the Detail dashboard, filtered for that Country. Returning to the Summary from the Detail dashboard is the challenge.

To return we can create a Back button. This is actually just another worksheet styled to appear like a button. Create a new calculated field with the formula ‘Back’. Drag that field to the Text shelf of a new worksheet, set it to be a chart type of Shape. Select an appropriate shape, such as a filled back arrow and size to fit the worksheet.

Tableau Back Button

Put this ‘button’ on to the Detail dashboard. On the Detail dashboard click Dashboard – Actions and set up a new Filter action. This time the Source Sheet is Detail, only the Back worksheet should be selected. The Target is the Summary dashboard, All Fields as the Target Filters. Make sure it’s set up to run action on Select.

The user will now be able to drill from the Summary to Detail and return to the Summary. The downside is with the Back button. Once it is selected – i.e. the first time clicked – it needs to be deselected to be able to be drill down again. Effectively this means the user needs to double click to go back, one click to reset then click again to go back. This double click isn’t good user experience. The good news is there is a way to make it single click.

Create two calculated fields, the first called ‘one’ with the value 1, the second called ‘two’ with the value 2. Set both fields to be dimensions (drag them up to Dimensions from Measures). Put ‘one’ to the Detail shelf of the Back worksheet.

On the Detail dashboard create a new action; Dashboard – Actions. A new Filter action is used to reset the Back button, the Source Sheet being the Back worksheet on the Detail dashboard. The Target Sheet is the Back worksheet – it is referencing itself. The Target filters are Selected Fields, Add Filter and the Source Field is ‘one’ and the Target Field ‘two’. ‘One’ has to be on the Detail shelf of the Back worksheet otherwise it causes an error.

Filter Action Back Button

The order of the 2 actions on the Detail dashboard is very important. Get the order wrong and the click on Back won’t take you back to the Summary, it only resets the button.

The order of the Actions is driven by the name of the action. Resetting the Back button needs to happen first followed by returning to the Summary sheet. Therefore prefix the name of the action to rest then back button with ‘a’ and the action back to summary with a ‘b’. The appropriate naming of the actions means the drilling down and back up again should work seamlessly.

Back Button Actions

When publishing to a Tableau server there are a couple of other points (frustrations) to keep in mind:

  1. ‘Show sheets as tabs’ has to be selected when publishing – if not it open the Detail in a new browser tab
  2. The browser tabs can be hidden by URL once published, as I have done above, add a URL parameter to the report URL, &:tabs=no.

The URL used to publish above, using the above technique to publish with sheet tabs then subsequently hiding the tabs, is https://public.tableau.com/views/DashboardNavigation/Summary?:tabs=no
 

Categories: Tableau, Tableau Server Tags:

Show the total for selected and unselected values

April 10th, 2017 No comments

Recently I came across a chart that showed the total for both the selected and unselected values. It’s actually very simple to create but did take a bit of thought to begin.

We want the values NOT selected to remain within the view. A standard filter removes the values completely. Therefore the unselected values must be calculated before the filter is applied.

Unselected Total Screenshot

(Image included as the Tableau Public version doesn’t open for users, especially in organisations with strictly controlled IT)

Read more…

Categories: Tableau Tags:

MS Access query isn’t in the Tableau table list

March 14th, 2017 No comments

Using MS Access in conjunction with Tableau isn’t always a painless experience, especially when it comes to the Access query. You might notice your Access query doesn’t appear in Tableau for you to choose. Alternatively you might find an Access query that returns records in Access returns zero records in Tableau.

All of this happens without any explanation, it’s a frustrating head-scratching moment. This article might help solve your problem.

 

My working Access query doesn’t return any records in Tableau

From my testing if there was a LIKE statement in the WHERE clause this caused Tableau to return no records. The query did appear in the Tableau table list but returned zero records. Options are: Read more…

Categories: Access, Tableau Tags:

Tableau Cannot use boolean type in IF expression

November 18th, 2016 1 comment

The “Cannot use boolean type in IF expression” error in Tableau calculated fields is quite self-explanatory. You’re not able to use fields that return true or false (i.e. boolean type) with IF statements in Tableau.

However there is a workaround. In many programming languages True or False can be represented as 1 or 0. Tableau recognises 1 and 0 as integers – therefore the way to get around the boolean type error is to wrap the boolean fields in INT().

For example create a simple calculated field with the formula 1=1, which is a boolean type.

Tableau boolean field

Next create a calculated field with an IF statement using the field, deliberately creating the Cannot use boolean type error.

Cannot use boolean type error

Converting the boolean field to an integer will remove this error.

Valid Tableau IF statement

Categories: Tableau Tags:

Simple way to add All to a parameter in Tableau

October 3rd, 2016 No comments

Back in 2012, I wrote a post describing an overly complex way to add ‘All’ to a parameter in Tableau. It is surprisingly simple to add All to a parameter if using the parameter to select a value from a dimension.

First create a patameter and add the values from the field and the value All.

Next create a calculated field to use the parameter. The generic formula to use ‘All’ in a Tableau parameter is:

[Parameter] = ‘All’ OR [Parameter] = [DimensionName]

The following is a very simple example based on the Superstore data. In this example the Segment is selected from a parameter, called SegmentParameter. Read more…

Categories: Tableau Tags: ,

Tableau tabcmd error code 16

July 25th, 2016 No comments

If you come across the tabcmd error code 16, Invalid username or password, it’s likely the error is caused by a special character in the password. For example %1 is interpreted by Tableau as a parameter. In the command line it should be converted to %%1, with the preceding % acting as an escape character.

Another special character Tableau can’t interpret is #. I was unable to find an escape character for that so just changed the password to make tabcmd work for the user with that password.

If anyone knows better ways around this than changing the password please write in the comments.

Categories: Tableau Tags:

Publish to Tableau Server from Alteryx using Run Command

March 30th, 2016 No comments

One of the things I often read about on the Alteryx forums is people asking how to publish to Tableau Server from Alteryx. Previously I wrote a post about publishing to Tableau Server from Alteryx using the Events functionality. A downside of this is the timing of the publish to Tableau – it either happens before or after the workflow has run. To publish to Tableau server from within an Alteryx module the Run Command tool can be used, meaning publishing from Alteryx to Tableau Server can happen from anywhere within the worklow. Read more…

Using Tableau SIZE to display nothing

January 4th, 2016 2 comments

This post is about the Tableau Size function being used to control whether data is displayed based on user filter selections.

This was inspired by a post on the Tableau Forum asking how to display nothing on a dashboard until the lowest level of detail is selected from a number of cascading quick filters.

In this case the questioner wanted a blank dashboard to be shown until a single City was selected, with the user using the filters to navigate from Region > State > City.

This is possible using the SIZE() function. Using ‘Compute Using’ enables the SIZE function returns how many of a given dimension are selected. Read more…

Categories: Tableau Tags:

Publish Tableau Data Extract directly from Alteryx

December 29th, 2015 No comments

For those Alteryx and Tableau Server users the ability to publish Tableau Data Extract (tde) files directly from Alteryx to the Tableau Server is a huge benefit for report automation. There are a number of good posts already published advising how this should be done, such as this excellent guide from Interworks. In this post I’ll replicate some of what is said in the post, should that link break at some point, and also add to it where I ran into difficulties.

To publish to Tableau Server from Alteryx requires using Tableau’s tabcmd. I expect it’s also possible using the REST API from Tableau but I was unable to work it out, so this post will focus on the tabcmd solution, which, in my opinion, is far simpler. If you’re not a Tableau Server administrator in your role you may need to install tabcmd.

Do the following to set up a workflow to publish directly to Tableau Server from Alteryx, using the workflow configuration Events:

Step 1

Open the Alteryx workflow that creates the tde file.

AlteryxOutputToTde

Step 2

In the Events section of the Workflow – Configuration add a Run Command. Also ensure ‘Enable Events’ is checked.

AlteryxWorkflowEvents

Step 3

Choose “After Run Without Errors”

AlteryxEventRunCommand

Step 4

Enter the location of the tabcmd.exe in the Command box

AlteryxEventTabcmd

Step 5

Enter the command line to execute in the Command Arguments section. I would recommend entering this into Notepad first and copying into the Command Arguments section.

For some unknown reason when I typed directly into Alteryx or into MS Word, copying to Alteryx, when the command was passed into tabcmd by Alteryx, the quotation marks in the command string weren’t being recognised causing it to fail. Entering the command into Notepad and copying it into Alteryx helped me get around that problem.

AlteryxEventCommands

Example tabcmd command line:

publish “\\NetworkLocation\TableauDataToPublish.tde” -s TableauServer -u UserName -p Password -t TableauSite -o -r “Data Sources”

Important things to note about the string:

  • publish = the tabcmd being called
  • -s = tells tabcmd which Tableau Server to use. This doesn’t need to be the “https://TableauSite” URL, it is better as the server name as entered to the Tableau Postgres database
  • -u = the username used to login to the Tableau Server (also needs to be set up on the Tableau Server)
  • -p = the password used to login to the Tableau Server
  • -t = the name of the Tableau Site on the Tableau Server to use
  • -r = the project name to publish to. If this is not included the Default project is used.
  • -o = Overwrite the existing tde file with the same name

There are a number of things that can be specified in the tabcmd command string. The Tableau help documentation has a comprehensive list of those items.

Step 6

Ensure the Timeout (in seconds) is set appropriately to give enough time for the tde file to be published to the Tableau Server

 

Now it’s complete, run the workflow and you should no longer need to manually publish tde files.

Important notes:

  • If using the Alteryx server the Alteryx service account will need setting up on Tableau Server with a Publisher role.
  • If tabcmd fails use the tabcmd.log file to see the error message(s). Alteryx will only tell you it failed, the tabcmd.log will tell you why it has failed. This is copied from the tabcmd overview on the Tableau website advising where to find the tabcmd.log:Status messages and logsWhen a command is successful, tabcmd returns a status code of zero. A full error message for non-zero status codes is printed to stderr. In addition, informative or progress messages may be printed to stdout.A full log named tabcmd.log that includes debugging, progress, and error messages is written to C:\Users\<username>\AppData\Local\Tableau.