BI Stories

Category: Quick Tips

  • Tableau Text Wrapping

    Recently someone noted that Tableau tables don’t do a great job displaying long text fields – the row height can be adjusted to accommodate any number of lines of text when it is wrapped, but it is fixed for the whole table, not dynamic per row. Look at the example below (just some old tweet dataset that was lying around), and you can see that all rows have exactly 3 lines, so in many cases there is blank space, or an overflow (two dots at the end):

    Is there a solution? Built-in there isn’t, but a couple of calculations can certainly help and format our text table more efficiently. This is how it’s done.

    First, I define an integer parameter called “Text length” and set the value to 40. I’ll use it later.
    Now I need to add an “expander table” to my data source. This is a simple table (I usually use Excel) with a column of numbers, from 1 to as many as needed. In the example I don’t expect to display more than 10 lines of text, so I limit the table to 10, but it can be a lot more. The relationship between the table and my data table is set as a full cross-join: 1 = 1

    What I’m going to do now is split the text field into separate lines, by finding the first space after every N characters (N being my parameter) and cutting the text at that point. For this I use 3 calculated fields:

    Cutoff N (this marks the first space after N * Number characters (or the end of the text)

    IF FIND([Text], ” “, [Number] * [Text length]) = 0 THEN LEN([Text])+1
    ELSE FIND([Text], ” “, [Number] * [Text length])
    END

    Cutoff N-1 (this is the previous space)

    FIND([Text], ” “, ([Number] – 1) * [Text length])

    Text N (cut out the text from [Cutoff N-1] to [Cutoff N]

    IF [Number] = 1 THEN LEFT([Text], [Cutoff N])
    ELSE TRIM(MID([Text], [Cutoff N-1], [Cutoff N] – [Cutoff N-1]))
    END

    Note that I’m using the [Number] field from my expander table to define the line number (N), and the number of characters in each line of text depends on the [Text length] parameter.

    Place [Number] on rows, then [Text N], and I get my table with the text field split nicely into lines:

    I can use the Text length parameter to play with the field width, and see what it looks like with different values.

    A few additional formatting steps:

    • Uncheck “Show Header” on the Number field in Rows.
    • Format the Borders so the lines within the text disappear.
    • Fine-tune the row shading.
    • If there’s no field after Text N, switch the mark type to Polygon so the “abc” disappears.

    And I have a formatted text table:

    Now let’s say that I need some data field to the right of my text – in this example I’ll use “Retweet count”. If I just add the field, it will display the same value for each line of text, so I need to use a table calculation:

    Retweets

    IF FIRST() = 0 THEN SUM([Retweet Count]) END

    I can add this, and then Edit Table Calculation using the following settings:

    This enables me to show the value only for one row, and my finished table, with the additional columns, looks like this:

    Summary

    The data in this sample dataset isn’t varied, with most tweets being around the same length, but you can see how a flexible number of text lines per data row can improve the look and feel of a Tableau table. So it’s not impossible – you need just a relatively simpke workaround. And that’s part of the power of Tableau: with a little imagination and some calculated fields, you can tweak your visualizations and solve many display problems without any coding.

  • Measure Value Indicators

    Measure Value Indicators

    As usual, this post started from a customer request. They had a nice table set up in Tableau, with Measure Names and Values (quite complicated ones), and they wanted to add a column with an indicator for each row – just a colored dot, but with 4 different colors.

    At first I said it couldn’t be done without re-engineering the whole table, but then I thought about it and suggested a solution similar to the following example, which is already working nicely in production.

    Let’s start with a simple table of measures, already colored using Separate Color Legends:

      Now we want to add a colored indicator for some of the rows, based on a calculated field – I’ll take the ranking of Profit Ratio as an example. To stretch Tableau to the limits, my requirement is to display an upward pointing triangle for the top 3 rows (Sub-Categories) in Profit Ratio, a downward pointing triangle for the bottom 3, and a circle for the rest. In addition, the top 3 should be colored in shades of blue, the bottom in red, and the others in light gray. Impossible? No!

      I start by defining my calculated field. What I need is a series of numbers, not the actual value (which is displayed separately in this case), and you’ll see later how these numbers support the visual element. I’m aiming for the numbers from 3 to -3:

      IF RANK([Profit Ratio]) <= 3 THEN 4 – RANK([Profit Ratio])
      ELSEIF RANK([Profit Ratio], “asc”) <= 3 THEN -4 + RANK([Profit Ratio], “asc”)
      ELSE 0
      END

      This gives me the values 3, 2, 1 for the top 3 rows, -3, -2, -1 for the bottom 3, and 0 for the rest.

      I can now leverage two of Tableau’s formatting features to create my indicators. First, the number format – I define a custom format:  ▲;▼;●

      What does this mean? Every number format is divided into three sections, separated by semicolons. The first is for positive numbers, the second for negative numbers, and the third for zeros. I’ve told it not to display the actual value, but a single character in each section (it could be any unicode text).

      Then, in addition, I can use Tableau’s color options and define a custom diverging color palette, with as many colors as I want. This is much easier in versions from 2025.3 onwards, that have the custom palette editor within Tableau, but can also be done by modifying the preferences file :

      Note that it’s important to define the same number of colors as numbers that I’m using for the indicators, and for the numbers to be evenly distributed. That enables me to fix the palette scale from the lowest to the highest number, and when I define Stepped Color with the corresponding number of steps, each number in my measure gets its own color:

      That’s it. I have my indicators in a simple Measure Names/Values table, without changing the whole structure of the worksheet.

      Remember that this technique has constraints: not more than 3 special characters or texts, and theoretically you can define any number of colors, but I haven’t checked Tableau’s limit (maybe 20?). But the bottom line is that it allows you to add colored shapes as indicators to a table of measures, as an additional column, and that can be very useful.

    • Sorting Date Filters

      Sorting Date Filters

      Recently I was asked, not for the first time, if Tableau could sort dates in a filter in descending order. Well – it can, sort of. But you need to know how to manipulate the fields a bit.

      Let’s take a simple case: I want to filter by month and year, but to see the latest month at the top of the list. So I drag my Order Date field to the Filters card, and select the Month / Year option.

      Now I can look at the menus in both the Filter card and the worksheet itself (after showing the filter), and there is no “Sort” option in either.

      So I’ll try to set the default sorting of the Order Date field to descending order, using the menu: Default Properties → Sort, and then checking the Descending option.

      Then I drag the field to Filters again, and… it stays in ascending order.

      But what if I choose the Individual Dates option? Suddenly, the dates appear in descending order, but I have every date (day) there. It doesn’t work if I select any other date level.

      So what’s happening here? Dates are a special type of field, and Tableau automatically aggregates them unless told otherwise – to years, months, or some other level. And it always sorts the aggregated dates in ascending order. The Individual Dates option is the only one that is not aggregated, so it retains the default sorting.

      How can we work around this problem? Let’s say that we need a filter displaying months, and sorted in descending order. We first need to create a field that has individual dates at the month level. There are two ways of doing this:

      • Create a calculated field, using the DATETRUNC function (more about that in a previous post), and also set its default format to something appropriate.
      • Create a Custom date field – one of the lesser known functions in Tableau. Select the “Months” option and “Date Value“.

      Now we can set the default sorting for the field, drag it to the filters, select the Individual Dates option, and the months will appear in descending order. Mission accomplished!

      Summary

      We now know that there are two conditions that have to be met in order to see dates in descending order in a date filter:

      1. The default sorting of the date field is set to “Descending”.
      2. The filter is not aggregated, but showing individual dates.

      We can manipulate the dates as necessary, but in the end those two conditions have to be met. All the rest is the usual Tableau trickery 😊

    • Don’t Extract an Extract

      Don’t Extract an Extract

      Recently, not for the first time, I was contacted by a Tableau customer: “We have a published data source, but after we refreshed the extract, the data in the dashbords isn’t updating!”. Why? Caching? A wrong connection? No – they had extracted the extract. As I’ve seen this mistake quite often, I’ll elaborate a bit.

      When you’re using a published data source (on Tableau Server or Cloud), the mode – live or extract – is defined in the data source. Afterwards, when connecting to the DS from a workbook, the connection has to be live, because you’re querying the DS directly in both cases. If it’s live, the queries to the database are sent through the DS definition, and if it’s an extract the data is queried from the extract on the server.

      The mistake is in thinking that because the data is extracted, the DS has to be defined as an extract within the workbook as well. That will actually create another extract, at the workbook level, that simply pulls all the data from the published DS and saves it within the workbook. And then, if you refresh the original DS data, the workbook is still using its own extract (which hasn’t been refreshed), and the dashboard data isn’t updated.

      So this is the correct configuration:

      In Tableau Desktop:

      And on the server it should look like this:

      And next time you encounter a workbook that’s “stuck” with outdated data, just make sure that you haven’t extracted the extract by mistake!

    • Text Bars

      Text Bars

      The most memorable Tableau trick that I encountered at DataFam Europe this year was something that I already knew of (vaguely), but seeing it twice brought it to my attention and now it won’t be pushed to the back of my trick cupboard again.

      This was presented both by Andy Cotgreave in DataFam Slam (minute 36:00), and by Nhung Le in Tremendous Tableau Tips (minute 20:00), with different context (you need to register for Salesforce+ to see the videos). I’ll discuss just the technique, and what it can be used for.

      So what are text bars? You can use a text field in Tableau to create a horizontal bar, and also manipulate its length using the data. This is possible due to the existence of the Unicode characters “█” (U+2588: Full Block) and “▒” (U+2592: Medium Shade).

      Let’s say we want to display our count of orders per customer, which is not a very large number, in bars within the table header. We create a calculated field:

      REPLACE(SPACE([No. of Orders]), ” “, “█”)

      The SPACE function returns a number of spaces as defined in the parameter that you give it (up to a limit, of course – don’t try to exceed 100 or so), and then we replace them with the Full Block character. The result is a text bar, that can be placed as a header in the rows of out worksheet:

      The label at the end is a simple concatenation:

      REPLACE(SPACE([No. of Orders]), ” “, “█”) + ”   ” + STR([No. of Orders])

      Another option is to use the text bar to display a percentage value, though it has to be rounded. In this example I am rounding to the nearest 5%, and filling up the 100% with the partially shaded bar:

      REPLACE(SPACE(ROUND(MAX([Profit Ratio], 0) * 20)), ” “, “█”)
      +
      REPLACE(SPACE(ROUND(20 – MAX([Profit Ratio], 0) * 20)), ” “, “▒”)

      Note the MAX function, which I am using to eliminate negative values.

      The end result is below. No concatenated label this time, because formatting the percentage value to a string is complicated, so I simply added the Profit Ratio value as the next field. The Segment field is there to show that the bars are within the header.

      Andy showed how text bars can be used within tooltips (before Viz in Tooltip was released), but I’m sure the community will think up other implementations, apart from the simple spicing up of a table that I showed, and probably using other special characters as well.

    • Tabbed Views

      Tabbed Views

      Every time we publish a workbook in Tableau, to Server, Cloud, or Public, we have the option to select the “Show sheets as tabs” checkbox. Obviously, this affects the display of the worksheet/dashboard tabs at the top of the view, but what else? And how has it changed over the years?

      Permissions

      A workbook with tabbed views counts as one entity for permissions. Any user who can access one view, can see all the others as well. They can also move between the views using the tabs, of course – that’s why the permissions can’t be separated: the display shows all the published tabs by default, so the user has access to them.

      If tabbed views are disabled, each view in the workbook has its own permissions. Users can’t move between them using tabs, and they have to exit back to the workbook level first – so permissions can be set separately, if required. I won’t go into detail about how to actually set permissions at the view level, the documentation is here.

      This enables the owner of the workbook, or administrator, to fine-tune the permissions if necessary, so some users don’t have access to certain dashboards, even in the same workbook – and in certain scenarios this is definitely a relevant use case.

      Dashboard Size

      The lesser known effect of setting tabbed views is the dashboard sizing. When tabbed views are on, all dashboards will be displayed as the same size in the browser, and if some have different sizes, the largest is used for all.

      What does this actually mean? If all dashboard sizes are set to “Automatic”, then there’s no problem. But suppose that you have one long-form dashboard in the workbook, with a fixed height of 3000 pixels that enables the user to scroll down. If tabbed views are on, all the dashboards will enlarge to 3000 pixels in height. And the same goes for the width, of course.

      If tabbed views are off, there are no such limitations, and every view can have a different size.

      Navigation

      One of the features that has changed over time is the navigation between views. In previous versions of Tableau, turning off tabbed views disabled the filter actions that navigate between views. I don’t recall in which version this changed, but now all the navigation options work even when tabbed views are off: filter actions, go to actions, and navigation buttons.

      This was actually a serious limitation in the past, because you nearly always had to compromise on something – the permissions, size, or navigation. Now that there are no limits on navigation, it’s much easier to decide which option to choose.

      One exception, of course – if tabbed views are off, you can’t navigate to a view for which you don’t have permissions. There are different results: a filter action simply won’t do anything, while a navigation button is grayed out and will display an “Access denied” message if clicked.

      Performance

      Even though there are still some resources on the internet saying that when you open a workbook with tabbed views, Tableau calculates all the dashboards in the workbook, that is totally incorrect.

      There is no difference in performance between tabbed and un-tabbed views. Tableau loads a view only when it is accessed by the user, and it always loads the workbook metadata when you access the first view – because it is stored as a single XML file. I have verified this using performance recordings, but it was always rather obvious: I’ve seen tabbed workbooks with 20-30 dashboards loading the first one in no time at all, so there’s no way all of them were calculated within that time. So don’t worry, if you have performance issues they won’t be caused by the tabs.

      Summary

      Tabbed ViewsNo Tabs
      PermissionsSame for all viewsCan be set separately
      Dashboard SizeBy largest viewIndependent for each view
      NavigationOKLimited only by permissions
      PerformanceOKOK

    • Nested Table Calculations

      Table calculations are one of the most used features of Tableau, but some of their capabilities are less well known, and I’d like to dwell on one of these: Nesting.

      Nesting table calculations occurs when one calculated field references another. That’s not a problem in itself, but what happens when we want each of these to behave differently? Compute the first using Table (across), and the second using Table (down)? I’ve found out that many developers don’t know how to do this, so here is a brief explanation.

      Let’s illustrate it using an example, based on Superstore data. I want to display sales by Product Sub-Category and period, and highlight the top 3 increases in sales for each period.

      I start by creating a simple table with sales by Sub-Category and Year:

      The first calculated field is the difference between each year and the previous year:

      Period Change:     ZN(SUM([Sales])) – ZN(LOOKUP(SUM([Sales]), -1))

      Note that this can easily be created using the Quick table calculation menu below, but then the next step would be impossible.

      Now I use this field in another table calculation:

      Period Change Rank:    RANK([Period Change]) <= 3

      This should return True for the top 3 changes per period (currently Year). I drag the field to Color, but after I set the colors that I want (green and white), I can’t get the correct results using the “Compute using” options.

      This is where the nested option is necessary, because the difference is calculated across the table (by rows), while the rank is calculated down (by columns). I open the “Edit table calculation” dialog box:

      Note that now there is a “Nested Calculations” dropdown, where I can choose the calculation option for each field, and now I can set “Table (across)” for Period Change and “Table (down)” for Period Change Rank. The result is now correct – the highlighted green cells are the top 3 increases for each year.

      To summarize – table calculations can be nested in two levels or more, and it is important to know that you have the option to set the “Compute using” option of each calculated field separately, enabling various types of analyses.

    • DATETRUNC

      One of the most underrated functions in Tableau, in my opinion, is DATETRUNC. Underrated, underused, and not understood. Recently I was disappointed to read a whole book about Tableau in which it wasn’t mentioned even once.

      Technically speaking, DATETRUNC “truncates” any date value to the starting point of the period: year, quarter, month, week, and so on. For example:

      NOW()29/08/2025 16:14:35
      DATETRUNC(“hour”, NOW())29/08/2025 16:00:00
      DATETRUNC(“day”, NOW())29/08/2025 00:00:00
      DATETRUNC(“week”, NOW(), “monday”)25/08/2025 00:00:00
      DATETRUNC(“month”, NOW())01/08/2025 00:00:00
      DATETRUNC(“quarter”, NOW())01/07/2025 00:00:00
      DATETRUNC(“year”, NOW())01/01/2025 00:00:00

      This is useful for calculations, but the real power comes from understanding that DATETRUNC is a hierarchical function, because it actually returns the parent of the date at the given level. So if we want to check if [date 1] and [date 2] are both in the same month – meaning that both have the same parent month – we can use   DATETRUNC(“month”, [date 1]) = DATETRUNC(“month”, [date 2])

      … instead of what I have seen too many times:  
      MONTH([date 1]) = MONTH([date 2]) AND YEAR([date 1]) = YEAR([date 2])

      If we want to group dates by month, we can use DATETRUNC(“month”, [date]), and that’s it – we’ve created a parent field for [date] at the month level.

      This is especially useful in creating relationships for date fields in data sources, for example when one table is at the timestamp level (transactional data) and the other at the quarterly level (quarterly goals). Just create a relationship calculation with DATETRUNC(“quarter”…) on both sides, and it works.

      There are countless other examples, but the important thing is to understand the main idea: DATETRUNC doesn’t just change the date value, it raises it to a higher level in the hierarchy.

      Now go out and use it.

    • Workbook Locale

      Many times, I’ve encountered Tableau developers struggling with the date formatting in their workbooks, mostly as it defaults to the US format (mm/dd) instead of what they need – and then they waste time setting custom formats for date fields in various worksheets.

      The solution to this, and one of the lesser known features of Tableau Desktop, is the “Workbook Locale” setting, located under the “File” menu.

      The “Automatic” option defaults to your computer’s regional settings, but if you select “More” you can choose your language, and then all date and number formats in the workbook will use that setting by default.

      Even less known is the fact that this setting, at the workbook level, overrides any other regional locale. So if you save the workbook with a locale other than “Automatic”, the date and currency format is fixed, even after publishing to Tableau Server or Cloud.

      The order of precedence is listed below, and documented here:

      1. Workbook locale (set in Tableau Desktop)
      2. Tableau Server User Account language/locale settings
      3. Web browser language/locale
      4. Tableau Server Maintenance page language/locale settings
      5. Host computer’s language/locale settings

      The bottom line – you can control everything using Workbook Locale, so use it, unless you need varying formats for users in different languages or countries, of course.

    • Tooltip = Axis ?

      Tooltip = Axis ?

      Tableau has many little quirks. With time you get used to them, but for new developers some of the small stuff can be very frustrating at first, and a nudge in the right direction always helps. So here’s one of them.

      In Tableau charts, any numbers appearing in tooltips are formatted using the “Axis” format, and not the default “Pane” format. So if you want your chart label to show “24.1%”, and the axis to have 0%, 5%, 10%, etc., your tooltip will show “24%”, which is a bit strange. See the example below:

      The best solution in such a scenario is to duplicate the relevant field, so basically you’re using two different fields – one for the label and axis, and another for the tooltip. Now you can set the axis format for the tooltip field (“Profit ratio (copy)” in the example below) without causing your axis to show unnecessary digits.

      Is there a reason for this functionality? Logic says that the tooltip format should be similar to the label, not the axis (which is usually more “rounded”), but maybe there’s something hiding behind it. And there’s been an Idea (now on the Salesforce IdeaExchange) about changing it for 12 years…