Archive

Author Archive

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:

Using Alteryx for Data Quality checks

November 15th, 2016 No comments

Data Quality is now a rapidly growing area in many Financial Services organisations. There are multiple vendors, such as Informatica and Ab Initio, with software specifically marketed as a data quality tool. Undoubtedly they are both great products, however they are expensive, in all likelihood in the majority organisations would take significant time before they are approved for purchase.

This is where Alteryx can step in, it’s a great tool to very quickly implement a data quality solution. The price point will not deter the majority of FS organisations and the ongoing administration is not an expensive burden.

Most data quality checks are highly specific, hence are bespoke and unable to be standardised. A business rule against a specific data point in a specific data set is a unique check. For example in the financial services world there are a number of rules defining an ISIN. Depending on the country code (first 2 letters of the ISIN) the remainder of the ISIN could have a specific format and a relationship to other data points, such as a CUSIP.

For something as simple as an ISIN there are actually many specific business rules to identify whether it is correct. In the data quality world each business rule is another quality check. These quality checks all require writing to mirror the business rules.

From the technical perspective Data Quality is actually an ETL process.

  • Extraction: the source data needs to be sourced and brought into the quality check
  • Transformation: the business rule check, transforming the source data into a check result
  • Load: the capture of the results

In Alteryx terms a workflow can hold a number of checks against the same data set. A data set is Input to the workflow, the business rules are written using Formula tools and the results are Output.

Significantly accelerate the writing of quality checks by creating an appropriate Data Quality Check template workflow in Alteryx. The inputs and checks (formula tools) are easily modified and the outputs should be standardised for streamlined reporting of the results.

Using Alteryx the technical side and automation of Data Quality checking can be achieved very quickly. Get in touch if you would like to learn more about our tactical Data Quality solution.

Categories: Alteryx 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: ,

Move or Copy files in Alteryx

July 26th, 2016 1 comment

In previous posts I’ve written about how to use the Run Command tool in Alteryx to publish a tableau extract to tableau server and how to unzip a file in Alteryx. Another great use for the Alteryx Run Command tool is to move and copy files.

To move files from a directory into another directory the following information is required:

  1. File path of file to move
  2. Destination directory

Set up the workflow in the following way:

1. Enter the above fields in a Text Input tool, creating the fields FullPath and DestinationDirectory

Alteryx Move Copy Text Input

Note: The backslash at the end of the DestinationDirectory is very important. Without that a new file would be created called Engine in D:\Alteryx. Read more…

Categories: Alteryx 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:

Unzip a file in Alteryx

April 21st, 2016 3 comments

I question often seen on the Alteryx forums is how to unzip a file in Alteryx.

In an earlier post I covered how to use the Alteryx Run Command tool to publish a Tableau Data Extract to the Tableau Server in an Alteryx workflow using TabCmd.

This post is similar but is showing how to unzip using the Run Command. The Run Command tool is able to call exe programs, meaning anything possible by exe is possible within Alteryx.

Credit to this solution needs to be given to jdunkerley79 who supplied the solution in the Alteryx forum: http://community.alteryx.com/t5/Data-Preparation-Blending/Can-Alteryx-unzip-a-file-as-part-of-the-workflow/td-p/10604

The first thing to do is install software to unzip. Currently I’m using 7-zip (http://www.7-zip.org/). This can be called via the command prompt.

Next is how to use this in Alteryx: Read more…

Categories: Alteryx Tags: