Set number, date, and currency formats

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.

ThoughtSpot supports number, currency, and percent formatting, at the data source level and at the answer level. The format pattern option combines number and percent formatting. You can override data source (“custom” in the UI) formatting at the answer level. In source level/ custom formatting, you can only define the format pattern (number and percent formatting) or currency option. If you set them both, the format pattern overrides your currency formatting.

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

.12

#%

12%

.12

#.00%

12.00%

.1234

#.##%

12.34%

If you are formatting percentages, you cannot have a space before the percent sign. #.00% is valid, but #.00 % is not.

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.

Change your preferred language in the profile

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:

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

03/15/2021

MMM

Mar

DD/MM/yyyy

15/03/2021

MM/dd/yyyy HH:mm

03/15/2021 12:05

DD/MM/yyyy HH:mm

15/03/2021 12:05

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.

You can see this custom date formatting when you use the date detailed keyword. Other date buckets, such as monthly or weekly, use the standard format. For daily dates, ThoughtSpot uses each user’s locale to determine whether to display mm/dd/yyyy or dd/mm/yyyy.

Set currency type

You can set a format for how currencies display in tables and charts. This setting affects the ThoughtSpot UI, the Data API, and the UI when embedding ThoughtSpot. 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.

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

  2. Select it to open the Specify Currency Type modal.

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

    Infer From Browser

    Your currency data is modeled based on 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. Select Ok to save your changes.

Limitations

If you use the From a column setup to specify the currency format, ThoughtSpot has the following limitations:

  • The currency column is automatically selected in every query that uses its linked column, and you cannot remove it.

  • In charts, the numbers in axes, data labels, and tooltips do not append the currency symbol. The currency symbol or ISO code is appended in the table view.


Related information


Was this page helpful?