Knowledge Base

Formatting Numbers and Dates

Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 20 Sep 2013

The Tableau Formatting window lets you change fonts, add banded shading, and modify the lines, colors, grid, and numbers and dates. This article focuses on the ways you can format numbers and dates. For example, suppose you want to show a measure with a dollar sign or use a custom date format. Or maybe you want to format a date that is aggregated to the M/D/Y level. With a few tricks you can get your numbers formatted just how you want without even writing a calculation.

Change a measure to currency

The view in the following image uses the Sales measure to show sales over time. Notice that the sales figures do not appear as if they are monetary values.

The following steps show you how to format the numbers as currency.

Step 1

Right-click the axis in the view and select Format.

Step 2

On the Axis tab, under the Scale area, select the Numbers drop-down list, and then select one of the following:

  • Currency (Standard) to add a dollar sign and two decimal places to the figures.

  • Currency (Custom) to specify the number of decimal places, how to show negative values, the units, whether to include a prefix or suffix, and whether to include thousands separators.

Specify a different locale for number formats

By default, Tableau uses your Windows Region and Language settings to format numbers. However, if the Windows settings do not reflect how you want to show them, you can set a different locale in the Tableau Format window.

The following steps show how to set Swiss German currency, using the same view as in the previous section as an example.

Step 1

Right-click the axis in the view and select Format.

Step 2

On the Axis tab, under the Scale area, select the Numbers drop-down list, and then select Currency (Standard).

Step 3

In the Locale drop-down list, items appear in a Language (Country) format. So for this example, you would select German (Switzerland).

The view updates to show the sales figures in Swiss Francs, formatted for German language.

Tip: You can change the default currency setting, so that every time you drag the Sales measure to a view that you create with this data connection, it shows the settings you want. In the Measures pane, right-click the Sales (or other monetary measure), and select Default Properties > Number Format.

Custom date formats

Using the same view example as in the previous sections, now format the date axis to show a custom format such as Sunday 1/1 or Aug 19 instead of just August.

Step 1

For Tableau Desktop 7.0 and later, right-click the field, and select the continuous Exact Date date level. The dates are now truncated to show each month and year along a continuous axis.
For Tableau Desktop 6.1 and earlier, right-click the field, and select All Values.

Step 2

Right-click the Order Date field, and select Format.

Step 3

You can specify a new date format using the Dates control in the Scale area of the Format window. Choose from a variety of formats or create a custom format. The table shown in the MSDN article User-Defined Date/Time Formats lists the values you can use for custom date formats. (The formats "oooo" and "aaaa" do not apply in Tableau.)


For this example we'll use the format DDDD DD so that it shows the Weekday and the Day.

The resulting view is shown below with the sales measure formatted as dollars and the date axis showing the weekday and the day.

Formatting distinct dates

In the previous example, the Date fields are truncated to the Month level, which creates a continuous axis. But what if you wanted to keep the distinct headers and still use a custom date format? For example, the view below shows the same view as before but this time it shows the sales for each distinct Month, Day, and Year.

Step 1

For Tableau Desktop 7.0 and later, right-click the date field on the Columns shelf, and select More Custom. In the detail drop-down list, select Month/Day/Year.
For Tableau Desktop 6.1 and earlier, right-click the date field on the Columns shelf, and select Measure > Minimum aggregation.

Step 2 (for 6.1 and earlier only)

Right-click the field again, and select Format.

As before, you can format the dates using the Date control in the Default section of the Format window. You can select from predefined formats or select Custom.

Did this article resolve the issue?

Attachments

Search Knowledge Base