Display formats

Display formats control how values are displayed within columns in the 1010data Insights Platform.

Syntax

The syntax for using display formats is:

[type:value;] [width:value;] [padwith:value;] [padto:value;] [dec:value;] [locale:value;] [numgroups:value]

where the value for each parameter is as follows:


parameter	`value`
`type`	Any Display format listed in the tables below (e.g., `nocommas`, `monthshort4y`)
`width`	The number of characters to display in the column. Valid values are 1 through 100. If the contents of a cell exceeds the specified width, the value is truncated. In this case, the number of visible characters is reduced by two, and "`>`" is appended to the value. For example, for `width:4`, the string `Example` would be displayed as `Ex >`. Clicking on the `>` displays the full contents of the cell in a separate window.
`padwith`	Used with numeric display formats (`num`, `commas`, `pct`, `currency`) and text formats (`char`) to specify that the displayed value should be filled or "padded" out to the column width with a particular character or sequence. When using `padwith` with `type:currency`, the padding is applied after the currency sign is applied, for example `*****$1,234.00`. However, if the pad character is`0`, the padding is applied before the currency is applied, for example `$000001,234.00`. (Available as of version 18.12) Note: `padwith` does not work with `date+time` formats.
`padto`	Used with numeric display formats (`num`, `commas`, `pct`, `currency`) and text formats (`char`) to specify padding that is fewer characters than the full column width. A negative value for `padto` specifies padding on the right. (Available as of version 18.12) Note: `padto` does not work with `date+time` formats.
`dec`	The number of decimal places to show (valid values are 0 through 15) Note: For integer values, a decimal point and that many 0's will be appended to the integer. The `dec` parameter does not affect text values.
`locale`	Used to modify the display behavior for numeric, `date`, and `date+time` values, overriding user preferences. Valid values are as follows: `US` - `,` is the thousands separator, `.` is the decimal separator, dates are `mm/dd/yy` or `mm/dd/yyyy` `UK` - `,` is the thousands separator, `.` is the decimal separator, dates are `dd/mm/yy` or `dd/mm/yyyy` `EU` - `.` is the thousands separator, `,` is the decimal separator, dates are `dd.mm.yy` or `dd.mm.yyyy` `INT` - (space) is the thousands separator, `,` is the decimal separator, dates are `dd/mm/yy` or `dd/mm/yyyy` Other locale specifications may be added in the future. (Available as of version 18.12)
`numgroups`	Used to specify alternate groupings of digits in numbers. Valid values are as follows: `INT` (the default value) - displayed as `123,456,789` `ALT` - displayed as `123,456,789` but `1234` (no comma in 4-digit numbers) `LC` - displayed as `12,34,56,789` (2-digit groups except for the final 3 digits) `WYZ` - `1,2345,6789` (4-digit groups) (Available as of version 18.16)

A single type, width, or dec format may each be applied to a particular column.

Note: Multiple parameters of the same format will be removed; only the first instance will be retained. For example, if the specified display format is format="type:num;type:char;width:3", it will be changed to format="type:num;width:3" when the query is submitted.

Examples

The secs(X) function returns the number of seconds since midnight for the time given as input. With no display format applied, a computed column using this function would use the default display format, num, and show values like 54,163 and 49,525.

To display the values in the computed column without the commas, use the nocommas display format:

<willbe name="secsexample" value="secs(thistime)" label="Secs since`midnight" format="type:nocommas"/>

Those same values would now be displayed as 54163 and 49525.

Display formats can also show only a certain number of decimal places for a column. For example, to show two decimal places for the numbers in a particular column, specify the following display format for that column:

<willbe name="margin" value="sales-cost" label="Margin" format="type:num;dec:2"/>

To pad the value 1234 the full width of a column with leading zeros, use the padwith option:

<willbe name="foo" value="1234" format="type:nocommas;width:8;padwith:0"/>

Integer display format

The following display format can be applied to values with the integer data type:


Display format	Example value	Displayed value	Notes
`months` (Available as of version 18.12)	`26`	`2 yrs 2 mons`	Integer is construed as a year-to-month interval representing a whole number of months and displayed as years and months.

Number display formats

The following display formats can be applied to values with the integer or decimal data type:


Display format	Example value	Displayed value	Notes
`num`	`1234567.89`	`1,234,567.89`
`nocommas`	`11,121,314.15`	`11121314.15`
`pct`	`0.9583`	`95.83%`
`days` (Available as of version 18.12)	`2.456`	`2 days 10:56:38`	The `dec:` option specifies the number of digits shown after the decimal place for seconds.

Currency display formats

Currency formatting uses an additional property: unit:[VALUE], where the [VALUE] is a valid ISO 4217 currency letter code, as shown below:

format="type:currency;width:10;unit:EUR"

Note: You can display the following currency types in a shortened format:


Full format	Shortened format
`format="type:currency;unit:GBP"`	`format="type:currency_gbp"`
`format="type:currency;unit:EUR"`	`format="type:currency_eur"`

For the full list of currency codes, see Currency codes (ISO 4217).

The default value if no code is provided is USD. The number of decimal places shown by default is determined by the standard for the particular currency code used. If a value is provided to the dec property, that value will override the default for that currency.

Currency display formats can be applied to columns of integer or decimal type:


Display format	Example `unit` value	Example value	Displayed value
`currency`	`EUR`	123456.789	`€123,456.79`

Date display formats

The following display formats can be applied to integers in the date form (YYYYMMDD):


Display format	Example value	Displayed value
`date`	`19981015`	`10/15/98`
`date4y`	`19981015`	`10/15/1998`
`ansidate`	`19981015`	`1998-10-15`

Note: If the date display formats are applied to numbers that are not in the date form, the behavior is unexpected.

Month display formats

The following display formats can be applied to integers in the month form (YYYYMM):


Display format	Example value	Displayed value
`month`	`200310`	`10/03`
`month4y`	`200310`	`10/2003`
`monthshort`	`200310`	`Oct03`
`monthshort4y`	`200310`	`Oct2003`
`monthshortdash`	`200310`	`Oct-03`
`monthshortdash4y`	`200310`	`Oct-2003`
`monthlong`	`200310`	`October03`
`monthlong4y`	`200310`	`October2003`
`monthlongdash`	`200310`	`October-03`
`monthlongdash4y`	`200310`	`October-2003`

Note: If the month display formats are applied to numbers that are not in the month form, the behavior is unexpected.

Quarter display formats

The following display formats can be applied to integers in the quarter form (YYYYQ):


Display format	Example value	Displayed value
`quarter`	`20032`	`2Q03`
`quarter4y`	`20032`	`2Q2003`

Note: If the quarter display formats are applied to numbers that are not in the quarter form, the behavior is unexpected.

Time display formats

The following display formats can be applied to integers in the time form (HHMMSS):


Display format	Example value	Displayed value	Notes
`hms24`	`224556`	`22:45:56`
`hms12`	`224556`	`10:45:56p`
`hm24`	`224556`	`22:46`	The time is rounded to the nearest minute.
`hm12`	`224556`	`10:46p`	The time is rounded to the nearest minute.

Note: If the time display formats are applied to numbers that are not in the time form, the behavior is unexpected.

Date+Time display formats

The following display formats can be applied to values with the decimal data type in the date+time form (XXXX.YYYYYYYYYYY):


Display format	Example value	Displayed value
`datehms24`	`-6906.38540509259`	`02/03/16_14:45:01`
`datehms12` (Available as of version 18.12)	`-6906.38540509259`	`02/03/16_02:45:01p`
`date4yhms24` (Available as of version 18.12)	`-6906.38540509259`	`02/03/2016_14:45:01`
`date4yhms12` (Available as of version 18.12)	`-6906.38540509259`	`02/03/2016_02:45:01p`
`ansidatetime`	`-6906.38540509259`	`2016-02-03 14:45:01`

Note: If the date+time display format is applied to numbers that are not in the date+time form, the behavior is unexpected.

Text display formats

The following display format can be applied to text:


Display format	Example value	Displayed value	Notes
`char`	`10/15/98`	`10/15/98`
`url`	`www.1010data.com`	`www.1010data.com`	Creates an `http:` link of the text. If the text is not a valid URL, the behavior is unexpected.
`email`	`info@1010data.com`	`info@1010data.com`	Creates a `mailto:` link of the text. If the text is not a valid email address, the behavior is unexpected.
`months`	`24`	`2 yrs 0 mos`