Posts in Category: Tableau

Tableau - Total one dimension in separate column

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

Gravatar

Modify Registry so twb and sql files show in preview

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

Gravatar

Tableau Server 'All Users' missing users

In an interesting twist, the system generated 'All Users' local group didn't have all the users that are listed in the Users tab for a site.  From running this SQL against the Postgres tables/views, confirmed that the users table and the group_users table were not aligned:

 
select case allusergrpmember when 1 then 'In All Users' else 'Not in All Users' end as GroupMembership, count(*) as ID_Count
from
(
select  _users.id, friendly_name, name, sum(case group_id when 2 then 1 else 0 end) as AllUserGrpMember from _users
 left join group_users gu on gu.user_id = _users.id
  where site_id = 1 
 group by 
 _users.id, friendly_name, name
 -- having sum(case group_id when 2 then 1 else 0 end) = 0
 order by friendly_name
 ) t
 group by allusergrpmember
 
So, the intermediate fix was to create a second 'All Other Users' local group to add the missing members.  Tableau was notified of the issue.
Gravatar
Page 2 of 5 << < 1 2 3 4 5 > >>
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2022 KB Earle Associates LLC | |