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;] [dec: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.

dec The number of decimal places to show (valid values are 0 through 9)
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.

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"/>

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
num 1234567.89 1,234,567.89
nocommas 11,121,314.15 11121314.15
pct 0.9583 95.83%

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"

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 -7691.56562470746 12/10/13_10:25:30
ansidatetime -7691.56562470746 2013-12-10 10:25:30
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.