Tech and T-Accounts

Tableau - Total one dimension in separate column 

Thursday, October 13, 2016 3:24:53 PM

Well, many of us come to Tableau after being frustrated with Excel.  Then, to our surprise, Excel does some things easily that Tableau doesn't seem to handle well.  There are lots of posts concerning how to create subtotals and grandtotals that are customized to a particular need.  However, the problem that I had was that I wanted the Totals for a particular dimension to show as a single column -- that is, if using something like Superstore Sales, I want a crosstab that has Region as the first column discrete dimension, then year(Ship Date) as the sub-column.  Relatively simple, until I wanted to see the order of the Columns to be like this:
 

Total Central East
2011 2012 2013 2014 2015 2011 2012 2013 2014 2015 2011 2012 2013 2014 2015
                             

I only show two of the Regions, but the idea remains -- how can I get the Years' Total to show up in this fashion within Tableau?

Well, the answer is in custom SQL -- I union the source table with itself, substituting the word 'Total' for the Dimension that is listed at the top -- in the above example, that would be Region.  So, using Superstore Sales, which is an Excel file, that means using the legacy connection so that Custom SQL can be created.

Basic overview of SQL:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select 'Total' as dimension1, dimension2, dimension3, measure1 from Source

What this does is adds a 'Total' value to all the rows for that one selected dimension, so that when the dimension is selected for a crosstab, not only are all the values present, but there is a new value also available - called 'Total'.  Using Sum on the measure will result in correct values for all the pieces and the Total, which then can be broken down by any of the other dimensions as a second, third, etc. column.

With the above in mind, we can get a bit fancier.  Tableau permits sending a parameter value to the connection -- great, now instead of creating a connection for each dimension that I might want to have a Total presented in the above manner, I can instead use a comparison statement to the parameter to determine whether that field value will be 'Total' or what is on the row.  For most SQL, this comparison would be done using a Case statement -- nothing too difficult about that.  But, Superstore Sales is an Excel sheet -- and Excel doesn't permit Case statements in Custom SQL.  Instead of a Case statement, a Switch statement is used in the SQL.  First steps first, however, create the Parameter and fill the list with the names of the Dimensions that you may want to show as a total.  (BTW, if you pick a field that normally would be shown as a date, it will instead be treated as a text field -- you'll have to create a calculated field to convert the text field to a date field should you want to use Tableau's built in date capabilities).

So, after creating the Parameter (in my case it was named 'Total Field Chooser'), the above SQL will look like this <>:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select
Switch(<Parameters.Total Field Chooser> = 'dimension1', 'Total', true, [Source].[dimension1]) AS [dimension1],
Switch(<Parameters.Total Field Chooser> = 'dimension2', 'Total', true, [Source].[dimension2]) AS [dimension2],
Switch(<Parameters.Total Field Chooser> = 'dimension3', 'Total', true, [Source].[dimension3]) AS [dimension3],
measure1 from Source

So, with the above in place as Custom SQL, I can modify the parameter and get the selected dimension filled with the word 'Total'.  One more task - create a calculated field that interprets the Parameter to select the Dimension -- for this, a simple Case statement in Tableau works fine. I named the calculated field 'Selected Total Field'

CASE [Total Field Chooser]
WHEN 'dimension1' then [dimension1]
WHEN 'dimension2' then [dimension2]
WHEN 'dimension3' then [dimension3]
END

Download the workbook (version 9.3) to see how this is built - it has three tabs, one connecting to Superstore Sales to show what Tableau's layout options are out of the box.  There is another with a 'fixed' column (Segment) that then has Total added.  Finally, there is a Custom SQL with Parameter driven version that permits a person to choose which Field is to have the Totals.  Enjoy.

Tableau Version 9.3 download

Modify Registry so twb and sql files show in preview 

Monday, April 18, 2016 7:49:00 AM

Use this approach to modify the Registry so that other file types are previewable as though they are text files.

Approach found here: http://superuser.com/questions/91804/windows-7-preview-other-file-types-as-text-in-preview-pane

Open the registry editor (regedit in the start menu search) and navigate to computer\HKEY_CLASSES_ROOT\.FILE_EXT, where FILE_EXT is the extension you want to add, such as .twb  or .tps or .tds or .sql

Make 2 new string values (REG_SZ) under the file extension's key, Content Type andPerceivedType
(note, only the PerceivedType is needed if Content Type already exists -- also note that PerceivedType has no spaces) 

Set the value of Content Type to text/plain and PerceivedType to text

For other Tableau items, like twbx, tdsx that are zip folders, add a PerceivedType as compressed in order for Windows File Explorer to provide insight into those files

Tableau Server - Bad behavior 

Tuesday, January 26, 2016 8:36:45 AM

What constitutes bad behavior on the part of Tableau Server users?  Here's my view, what's yours?

  1. Old reports that aren’t used yet still run extracts.
  2. Failed extracts, especially repeat offenders on PROD.
  3. Huge number of subscribers to reports which take minutes to render.
  4. DEV versions on PROD server that aren’t actively being UAT.
  5. Repetitive extracts embedded in workbooks rather than as a datasource.
  6. Huge extracts when someone has access to a more cost effective db solution.
  7. Custom SQL when a Tableau build with referential integrity could have been done.
  8. Thirty or so connections in a single workbook.
  9. Using Projects instead of Sites.
  10. Governing AD groups by an organization distant from the managers of the AD group.
  11. Leaving departed users licensed on a server.
  12. Poor Tableau performance from attempting to design according to another product’s ‘best practices’.
Page 1 of 5 1 2 3 4 5 > >> 
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2017 KB Earle Associates LLC | |