I like to think I know a lot about Tableau but when I read a post by Ken Flerlage, or see a #WorkoutWednesday by Luke Stanke, I realize there is so much more to learn. Every project that I work on comes with its own set of challenges and, subsequently, new things learned. Here are the Top Five things I’ve learned about Tableau recently!
- Handling Fiscal Dates
- How Tableau Server reads field aliases
- Getting containers to open and close (completely)
- Sizing map icons appropriately
- How to blend data sources on dates and not lose your mind
Fiscal Dates
If you are involved with finance or accounting, odds are you will need to use fiscal dates. Essentially, this means that you will need to convert your date fields into a more digestible format that aligns with your company’s fiscal year structure.
For example, if a company separates months into fiscal months, the Month([Date Field]) function in Tableau will not reflect the correct fiscal month. It will show the calendar month for your [Date Field]. Say your fiscal month is five weeks long, which includes the first couple days of the next calendar month, you will not be able to use the standard Tableau date functions. Here are two different solutions I have used to solve this issue.
First, there is a manual solution involving DATEPART calculations and text logic. I warn you that this approach is not performant, but it is a solution. By breaking up the week numbers into the company’s fiscal months, you can then use this field as the Fiscal Month formula. See below.
// IF DATEPART('week',[Date Field])>0 and DATEPART('week',[Date Field])<=5 THEN "January"
ELSEIF DATEPART('week',[Date Field])>5 and DATEPART('week',[Date Field])<=9 THEN "February"
ELSEIF DATEPART('week',[Date Field])>9 and DATEPART('week',[Date Field])<=13 THEN "March"
ELSEIF DATEPART('week',[Date Field])>13 and DATEPART('week',[Date Field])<=18 THEN "April"
ELSEIF DATEPART('week',[Date Field])>18 and DATEPART('week',[Date Field])<=22 THEN "May"
ELSEIF DATEPART('week',[Date Field])>22 and DATEPART('week',[Date Field])<=26 THEN "June"
ELSEIF DATEPART('week',[Date Field])>26 and DATEPART('week',[Date Field])<=31 THEN "July"
ELSEIF DATEPART('week',[Date Field])>31 and DATEPART('week',[Date Field])<=35 THEN "August"
ELSEIF DATEPART('week',[Date Field])>35 and DATEPART('week',[Date Field])<=39 THEN "September"
ELSEIF DATEPART('week',[Date Field])>39 and DATEPART('week',[Date Field])<=44 THEN "October"
ELSEIF DATEPART('week',[Date Field])>44 and DATEPART('week',[Date Field])<=48 THEN "November"
ELSEIF DATEPART('week',[Date Field])>48 and DATEPART('week',[Date Field])<=52 THEN "December"
END
The second solution is more common for enterprise solutions. Most of the time, a fiscal date translation table will be available for you to join onto your existing table. This table will include a column for the date and the detail columns that will tell you what fiscal month, week, or quarter that the date translates to. This solution is more practical, performant, and easier for development.
Tableau Server and Field Names
In a recent project, I used Tableau Prep to create my desired data set. This workflow included renaming fields to a more back-end appropriate structure such as vendor_name and vendor_id instead of names that might look better displayed on a dashboard (Vendor Name and Vendor ID).
At some point during the project, I manually renamed those fields to be more palatable to both end-users and developers. I created several copies of this workbook for different business units and when the time came to publish to Tableau Server I thought things would go smoothly. WRONG!
I published the data source to Tableau Server and then tried to connect to this newly published data source in Tableau Desktop but when I clicked Replace Data Source, my workbook pages went blank and all my calc pills turned Red. Oh no…
Turns out, if you rename fields, when you publish your data source to Tableau Server your fields revert to their original names, but the fields in your calculations do not revert to those names. So if your initial calculation had Vendor Name in it, it will continue to have the text Vendor Name in there, but that is no longer a field in your data set. This will cause your calculation to error due to an invalid field.
Getting Containers to Disappear Completely
A creative way to get more data in your dashboards without taking up real estate is to include collapsible containers to allow for drill down into specific areas of the dashboard. Because you’re trying to hide that this data is available in the default view, you will want the container to be completely hidden. I was having an issue getting this to happen until I found out this little trick.
First, I created a calculated column named Collapsible Container like below utilizing a blank string parameter.
// IF [Blank Parameter]="" THEN TRUE ELSE FALSE END
I then create a Parameter Action that drives the [Blank Parameter] value via a click. Clicking on one of the dashboard actions will drive a value to that parameter. Drag [Collapsible Container] to the filter pane and select FALSE.
Next, double-click into the column and rows and put double quotes (“”) into both. If you do not have these in there, Tableau will not collapse your container. It’s as if there is nothing there to collapse, then the container structure remains. By putting these in there, Tableau will use the [Collapsible Container] filter to NULL out all the values in this worksheet and nothing will show in your dashboard.
Sizing Icons Differently for a Dimension
When plotting points on a map, it’s easy to want to use size to tell part of the story. There are times where you will not want to size all the dots in a dimension the same. For example, if you want to size one value in the dimension by sales and not size the other dimension types, we can use the formula below.
// IF [Type]="Type A" THEN { FIXED [Category ID]:AVG([Sales])}
ELSEIF
ELSE 1 END
The formula above will allow you to size your Type A icons by Avg Sales and then the rest will return as 1. Because you are still returning a value of 1, you are able to size that dot using the Edit Sizes panel as shown below.
Blending Data Sources with Dates
Getting data blending to work can be tedious in Tableau, especially if you’re trying to do calculations on either side of the blend. On a recent project I needed to show the Sum(Sales) from one data set along with Sum(Defect Product) from another data set and how those correlated by month. In order to get both of those metrics to show, we needed to create a data blend on dates.
I started by trying to use the normal blend method of choosing the different parts of the date to join on, but this kept giving me incorrect results. The calculations would always be off for one of the datasets on either side of the blend. This caused me to use the workaround below. Although it’s simple, it helped me move past this issue.
// DATEPART('year',[Date Field A]) = DATEPART('year',[Date Field B])
// DATEPART('month',[Date Field A]) = DATEPART('month',[Date Field B])
For some reason Tableau would not properly recognize the blends on date parts, so I created calculated fields that parsed out the part of the date that I needed and used those in my Edit Relationships panel. I do not know what bug was happening in Tableau, but this was caused by blends working perfectly.
Do you have more questions about Tableau? Talk to our expert consultants today and have all your questions answered!
