Learn how to set key formats for column values.

You can set number, date, and currency display formats. These formats define how these value types display in tables and charts.

Number formats

You can set a format for how numbers are displayed in tables and charts. For example, you can display numbers with a different number of digits after the decimal point, based on the data modeling setting Format Pattern. You can use any of the supported number formats for delimiters and number of digits to show using Java Decimal Notation. Currency symbols are not supported.

The system has default values which are:

#,### For integer data types INT and BIGINT. As you can see, these can only contain numbers, alpha characters are not permitted.
#,###.00 for decimal data types DOUBLE and FLOAT.

These are some examples of formats you can use:

Stored Value Format Pattern Display Value
12345.6789 #,##0.## 12,345.68
12345.6789 #,##0.### 12,345.679
12345.6789 #,##0.00000 12,345.67890
12345.6789 #,##0 12,346
12345.6789 #,##0.00 12,345.68
12345 #,##0.## 12,345
12345 #,##0.00 12,345.00

Profile-based number formatting

Number formatting is set by default based on your ThoughtSpot profile’s Preferred locale setting. You can set this value to accommodate your geographic locations.

For example, if you are using ThoughtSpot in the US, the number formatting should look like this: xxx,xxx.xx. And in Europe, it should look like this: xxx.xxx,xx.

Date formats

The Format Pattern option in the UI or in the model file allows you to specify how ThoughtSpot should display dates in tables and charts. For example, you can display dates in a standard European or US format based on the data modeling setting Format Pattern. These are some examples of formats you can use:

Format mask Description
YYYY or yyyy four digit year such as 2017
YY or yy last two digits of year such as 17
M month with no leading zero 1-12
MM Two digit month 01-12
MMM Three letter month such as Jan
D Day of year without a leading zero 0-365
DD Day of year with up to one leading zero 01-365
DDD Day of year with up to two leading zeroes 001-365
d Day of month with no leading zero 1-31
dd Two digit day of month 01-31
HH Two digit 24 hour representation of hour 00-23
hh Two digit 12 hour representation of hour 01-12
H 24 hour representation of hour with no leading zero 0-23
h 12 hour representation of hour with no leading zero 1-12
mm Minutes 00-59
m Minutes with no leading zero 0-59
ss Seconds 00-59
s Seconds with no leading zero 0-59
a AM/PM indicator

Valid delimiters include most non-alphabet characters. This includes but is not limited to:

  • \ (forward slash)
  • / (backward slash)
  • | (pipe symbol)
  • : (colon)
  • - (dash)
  • _ (underscore)
  • = (equal sign)

Examples of valid format masks you can produce for display are as follows:

  • MM/dd/yyyy
  • MMM
  • DD/MM/yyyy
  • MM/dd/yyyy HH:mm
  • DD/MM/yyyy HH:mm

You can change the date format used to display a column’s values for a single table or Worksheet or, by editing the data model, for the entire ThoughtSpot instance. To edit the data model file, you must have administrative privileges. To change the date format, follow these steps:

  1. Decide if the change is for a specific table or Worksheet, or for the entire instance.

  2. Find the Format Pattern for the column.

    This is either a column in a single table or Worksheet or a column in the data modeling file.

  3. In the column, enter the format you want to use.

  4. Save your changes.

    If you are using a data-modeling file you must upload the new file to your installation.

Set currency type

You can set a format for how currencies display in tables and charts when using the ThoughtSpot Data API or embedding. For example, you can display currencies in a standard European Euro or US Dollar format, based on the data modeling setting Currency Type.

You can change the currency format used to display a column’s values for a single table. When you specify the currency type of your data in the Columns settings, your currency data will only display the correct format and currency code in the embedded use case. Currency specific symbols are available in the non-embedded use case as well, but they are not localized.

All users are treated as if they are in en-US locale unless they are in embed mode and their browser configuration tells ThoughtSpot that they are in some other locale. For example, 100 Polish Zloty appears as 100zł to a user in Poland, but without localization enabled, it appears as PLN 100.

This subtle difference can be seen when you use the REST API. See the ThoughtSpot Application Integration Guide for more information on the API.

  1. Find the Currency Type for the column whose display format you want to change.

  2. Click it to open the Specify Currency Type menu.

  3. Select one of the following ways you would like to change the format.

    Option Description
    Infer From Browser Your currency data will be modeled upon the locale of your browser setting.
    From a column Your currency data will be modeled upon the existing currency information in the selected column. This option is disabled if there is no VARCHAR column to choose from.
    Specify ISO Code Your currency data will be modeled upon your selection from the available currency code choices.

  4. Click Ok to save your changes.

Model the data for searching