toList

The toList(list [,options]) function is the most commonly used formula table function. It generates a unique, sorted, and grouped list from the values in a JavaScript Array or tableLens.

In most cases, you will use toList() to obtain distinct values from a query array when you create header rows/columns. For example, “toList(q['state'])” obtains a distinct list of states in ascending order from query variable “q”.

The full syntax of the function is as follows,

toList(list, 'options string');

where options string values are as follows (multiple options are separated by a comma delimiter):

sort = asc/desc/false: Sort the values in ascending or descending order. Default is asc.

sorton: Sort the values according to a specified measure, e.g., sorton=sum(Total). Sorting by sorton is applied before Top/Bottom-N filtering implemented by maxrows.

sorton2: Sort the values according to a specified measure, e.g., sorton2=sum(Total). Sorting by sorton2 is applied after Top/Bottom-N filtering implemented by maxrows.

remainder: Specifies the label for the 'Others' group when Top/Bottom-N filtering is in effect. If not specified, the 'Others' group is not displayed.

maxrows = num: Limits the number of rows returned.

distinct = true / false: Retrieve only distinct values. Default is true.

date = year / quarter / month / week / day / hour / minute / second / weekday / monthname / weekdayname: Group date values according to specified period, and return the period designation.

rounddate = year / quarter / month / week / day / hour / minute / second / weekday / monthname / weekdayname: Group date values according to specified period, and return the rounded date value.

The rounddate option is useful when you want to group by month and year, (e.g., Jan 2005 and Jan 2006, etc.). For example, if the 'Order Date' field in a query has the following values,

[Jan-2-2002, Feb-21-2004, Feb-25-2004, Nov-25-2005]

then the toList function with a rounddate grouping of month,

toList(q['Order Date'], 'rounddate=month');

returns a unique list of dates containing the first day of the month of the given year:

[Jan-1-2002, Feb-1-2004, Nov-1-2005]

See Also

Referencing a Cell with Relative Parent Group Reference, for a complete example.

Extracting Data from a Query, to extract query data into a JavaScript Array.

<< mapList © 1996-2013 InetSoft Technology Corporation (v11.4) 3.7.6 Visual Properties >>