This blog post is a direct follow on from my other post about tableau tips and tricks
String fields as hours and minutes
1. How to make hours and minutes in Tableau using having time, or date-time type fields in Tableau.
First, some data prep is required. Ensure you have a column with number values, so 219 as the first value for example.
The next step is to make a calculation in tableau like this:
Float(if sum([FIELD])>60 then str(int((sum([FIELD]))/60))
+ if len(str((sum([FIELD]))-int((sum([FIELD]))/60)*60))=1
then str((sum([FIELD]))- int((sum([FIELD]))/60)*60)
else (str((sum([FIELD]))- int((sum([FIELD]))/60)*60)) end
else str((sum([FIELD]))- int((sum([FIELD]))/60)*60) end)
else null end
you will need to adjust the axis that this new filed is on, to do this go into the axis number formatting and enter: #0h 00m into the custom format. This should label the axis in a hours and minutes format aswell.
There are a few downsides to doing this trick in tableau. The axis will still be 0 to 100, so in the axis settings you will need to put tick marks interval on something like 100 (1 hour) so that we don’t see 1h 90m and then 2h 00m next to it.
Another downside is that going from 50m to 1 hour will look like quite a jump as everything from 61m to 99m will be ‘technically’ not there, but still visible.
LOD trick
2. One of the best tricks I know is an LOD calculated field, that involves a parameter. So say you have data that contains a few countries that are grouped by a common region. When I click on a country I want to see all the countries within the region that, that country is a part of. This is how to do it:
[REGION]= {max(if [COUNTRY]=[COUNTRY parameter] then [REGION] end}
Drag this field onto a sheet as a filter and set the filter to TRUE. Have a dashboard action to change the country parameter when you select a country, or manually change it. This can be a useful tool to compare various things in a similar group.
Effective workbook building
3. how to go about making a workbook effectively. Someone once told me that way they approached making fields and parameters in tableau was to do it as you would in coding. So if you create a parameter, put a prefix if “P_” to it, so if you need to search for it P_ will give you all the parameters in the workbook. Below I’ll give an example of what you can prefix to different things to help you (and the next person who might use your workbook).
Sets: “S_”
Fields used for Filters “F_”
Fields used for colours “C_”
It doesn’t have to just apply to things you want to throw onto the marks on the worksheet, you order them if they are part of notes “N_” or as a header “H_” or a specific dashboard “D1_”.
If you structure it this way it will help you in the future. Tableau by default should group the fields together alphabetically, so all the filters will be in a cluster, for example. If you don’t want to see every field at the same time you can always group them using folders in Tableau, by clicking group by folders, instead of by data source. Then create folders and drag each one into them.
Obtaining data from workbooks
4. how to get the data from a workbook. In one of my dashboards, I wanted to analyse the data from each makeover Monday, but sometimes the data didn’t exist. To remedy this I looked up workbooks on Tableau public and stole the data from them by deleting all calculated fields (to only be left with the original data) and viewed the data and exported to a csv. This is very useful if you want to validate the data from any workbook because the numbers don’t seem correct. Be sure that you adjust the row amount (see below) as Tableau will show the first 10,000 rows. If you want to see everything enter a very large number.
Custom colour palette creation
5. How to create your own colour palette. Locate the preferences file (preference.tps) in your tableau repository, which you will need to open in notepad or any other text editing software. Open it, and take note of the structure.
To add a regular custom colour palette, copy the text below and add your own hex codes. I have added comment using // to tell me what the colour actually is. Hex codes can be found in Tableau or online
<color-palette name=’New Colour palette type=’regular’ >
<color>#E79F26</color> //Orange
<color>#5FB2E6</color> //Sky Blue
<color>#069F73</color> //Blueish Green
<color>#F1E545</color> //Yellow
<color>#0973B0</color> //Blue
<color>#D36027</color> //Vermilion
<color>#CC79A7</color> //Reddish Purple
<color>#F5F5F5</color> //Grey
</color-palette>
To add a diverging custom colour palette, copy the highlighted items and add in items inbetween with the hex codes to match. It is borderline the same except for the type being equal to ‘ordered-divereging’
<color-palette name=’Diverging type=’ordered-diverging’ >
<color>#00392C</color> //Dark Green
<color>#F5F5F5</color> //Grey
<color>#532C01</color> //Brown
</color-palette>
The last type of palette you can have is ‘ordered-sequential’. this type of palette shows a single color, varying in intensity. This type of colour palette is used for continuous fields, typically for measures.
<color-palette name=”My Sequential Palette” type=”ordered-sequential” >
<color>#eb912b</color>
<color>#eb9c42</color>
<color>#ebad67</color>
<color>#eabb86</color>
<color>#eacba8</color>
<color>#ebd8c2</color>
</color-palette>
I hope these were beneficial tips, any issues feel free to try to contact me
