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 |
|
for decimal data types |
These are some examples of formats you can use:
| Stored Value | Format Pattern | Display Value |
|---|---|---|
12345.6789 |
|
12,345.68 |
12345.6789 |
|
12,345.679 |
12345.6789 |
|
12,345.67890 |
12345.6789 |
|
12,346 |
12345.6789 |
|
12,345.68 |
12345 |
|
12,345 |
12345 |
|
12,345.00 |
.12 |
|
12% |
.12 |
|
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.
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 |
YY or yy
|
last two digits of year such as |
M
|
month with no leading zero |
MM
|
Two-digit month |
MMM
|
Three letter month such as |
D
|
Day of year without a leading zero |
DD
|
Day of year with up to one leading zero |
DDD
|
Day of year with up to two leading zeroes |
d
|
Day of month with no leading zero |
dd
|
Two digit day of month |
HH
|
Two digit |
hh
|
Two digit |
H
|
24 hour representation of hour with no leading zero |
h
|
12 hour representation of hour with no leading zero |
mm
|
Minutes |
m
|
Minutes with no leading zero |
ss
|
Seconds |
s
|
Seconds with no leading zero |
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:
-
Decide if the change is for a specific table or worksheet, or for the entire instance.
-
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.
-
In the column, enter the format you want to use.
-
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.
-
Find the Currency Type for the column whose display format you want to change.
-
Select it to open the Specify Currency Type modal.
-
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
VARCHARcolumn to choose from. - Specify ISO Code
-
Your currency data will be modeled upon your selection from the available currency code choices.
-
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
