Skip to main content

Number Formatting

Formats for numbers and dates are defined using the column names in your SQL query.

A format tag can be appended to your column name to ensure the right format (see table below for accepted tags).

Format tags are appended with an underscore: for example, to append the percentage format to a column named growth, it would be growth_pct.

Formatting can be configured in the Value Formatting Section of the Evidence Settings.

Format tags are case-insensitive, so growth_pct and GROWTH_PCT are equivalent.

Built-in Value Format Tags

Evidence supports a variety of date/time, number, percentage, and currency formats. You can find the full list of format tags below

Custom Value Format Tags

Custom formats can be added in the Value Formatting Section of the Evidence Settings. Formats can be coded using Excel style custom format codes.

Title Formatting

When creating a table, Evidence formats column titles based on the name of the column and its format tag. Format tags that do not add to the meaning of the column name are not printed as part of the title.

Examples

Column NameFormatted Title
sales_usdSales ($)
customer_idCustomer ID
growth_pctGrowth
customer_number_num2kCustomer Number

Large Numbers

Evidence automatically formats large numbers into shortened versions based on the size of the median number in a column (e.g., 4,000,000 4M).

You can choose to handle these numbers differently by choosing a specific format code. For example, if Evidence is formatting a column as millions, but you want to see all numbers in thousands, you could use the num0k format tag, which will show all numbers in the column in thousands with 0 decimal places.

Format Reference

Dates

Format TagFormat CodeExample InputExample Output
dddddd2022-01-09 12:45Sun
dddddddd2022-01-09 12:45Sunday
mmmmmm2022-01-09 12:45Jan
mmmmmmmm2022-01-09 12:45January
yyyyyyyy2022-01-09 12:452022
shortdatemmm d/yy2022-01-09 12:45Jan 9/22
longdatemmmm d, yyyy2022-01-09 12:45January 9, 2022
fulldatedddd mmmm d, yyyy2022-01-09 12:45Sunday January 9, 2022
mdym/d/y2022-01-09 12:451/9/22
dmyd/m/y2022-01-09 12:459/1/22
hmsH:MM:SS AM/PM2022-01-09 12:4511:45:03 AM

Currencies

Supported currencies:

CodeCurrency
USDUnited States Dollar
AUDAustralian Dollar
BRLBrazilian Real
CADCanadian Dollar
CNYRenminbi
EUREuro
GBPPound Sterling
JPYJapanese Yen
INRIndian Rupee
KRWSouth Korean won
NGNNigerian Naira
RUBRussian Ruble
SEKSwedish Krona

In order to use currency tags, use the currency code, optionally appended with:

  • a number indicating the number of decimal places to show (0-2)
  • a letter indication the order of magnitude to show ("","k", "m", "b")

For example, the available tags for USD are:

Format TagFormat CodeExample InputExample Output
usdauto412.17$412
usd0$#,##07043.123$7,043
usd1$#,##0.07043.123$7,043.1
usd2$#,##0.007043.123$7,043.12
usd0k$#,##0,"k"64301.12$64k
usd1k$#,##0.0,"k"64301.12$64.3k
usd2k$#,##0.00,"k"64301.12$64.30k
usd0m$#,##0,,"M"4564301.12$5M
usd1m$#,##0.0,,"M"4564301.12$4.6M
usd2m$#,##0.00,,"M"4564301.12$4.56M
usd0b$#,##0,,,"B"9784564301.12$10B
usd1b$#,##0.0,,,"B"9784564301.12$9.8B
usd2b$#,##0.00,,,"B"9784564301.12$9.78B

Numbers

Format TagFormat CodeExample InputExample Output
num0#,##011.2316811
num1#,##0.011.2316811.2
num2#,##0.0011.2316811.23
num3#,##0.00011.2316811.232
num4#,##0.000011.2316811.2317
num0k#,##0,"k"6420164k
num1k#,##0.0,"k"6420164.2k
num2k#,##0.00,"k"6420164.20k
num0m#,##0,,"M"4253948343M
num1m#,##0.0,,"M"4253948342.5M
num2m#,##0.00,,"M"4253948342.54M
num0b#,##0,,,"B"13849375841B
num1b#,##0.0,,,"B"13849375841.4B
num2b#,##0.00,,,"B"13849375841.38B
id0921594675921594675
fract# ?/?0.25 1/4
mult#,##0.0"x"5.325.3x
mult0#,##0"x"5.325x
mult1#,##0.0"x"5.325.3x
mult2#,##0.00"x"5.325.32x
sci0.00E+016546.15611.65E+4

Percentages

Format TagFormat CodeExample InputExample Output
pctauto0.73173.1%
pct0#,##0%0.73173%
pct1#,##0.0%0.73173.1%
pct2#,##0.00%0.73173.10%
pct3#,##0.000%0.73173.100%