Expressions: List Functions

The following List functions are available to use in FastStats' Expressions:

RankList

This function can be used to find out the rank of a value in a list of values. Although not restricted to the case described here, the most common use is to rank Expressions in Derived Cubes.

  • RankList(numeric, “direction”, “tie strategy”, list) - NUMERIC

Where:

Parameter 1 is the number to rank

- if this number is not in the list then the result will be missing value

Parameter 2 is the direction of ranking

- either “ascending” or “descending”

- single letter “a” and “d” can be used

Parameter 3 is the way to break ties

- either “competition” or “dense” are the allowed strategies

- single letter “c” and “d” can be used

Parameter 4 is a numeric list

- currently such a parameter uses one of the CubeRange/PeriodToDateCubeRange Expression functions

Example

To rank across a whole dimension:

  1. Create a Cube and add Income as a dimension

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

NTileList

This function can be used to find out the Ntile of a value in a list of values. Although not restricted to the case described here, the most common use is for calculating Ntiles in Derived Cubes.

  • NTileList(numeric, “direction”, number of tiles, list) - NUMERIC

Where:

Parameter 1 is the number for which to work out the Ntile

- if this number is not in the list then the result will be missing value

Parameter 2 is the direction of ranking

- either “ascending” or “descending”

- single letter “a” and “d” can be used

Parameter 3 is the number of Ntiles

- typically this is 100, but you can choose any numeric you like

Parameter 4 is a numeric list

- currently such a parameter uses one of the CubeRange/PeriodToDateCubeRange Expression functions

Example

To create NTile across a whole dimension:

  1. Create a Cube and add Income as a dimension

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

FilterList

This function takes a list of values and removes some of its values. The primary use case is to filter outlier values from a list of values.

  • FilterList reduces the set by selecting based on value

  • FilterList (Lower,Upper,Include,{List}) - LIST

    - ‘lower’ and ‘upper’ are both numeric values

    - if Include is true then include Lower <= x <= Upper, otherwise exclude this range

    - the List parameter is created from a CubeRange/PeriodToDateCubeRange Expression function

Example

To find the max of Destinations but filter out values < 1000 and > 100000:

  1. Create a Cube and add Destination as a dimension

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

TrimList

This function takes a list of values and removes some of its values.

  • TrimSet reduces the set by selecting based on number of items

  • TrimList(Lower,Upper,Include,{List}) – LIST

    - if ‘lower’/’upper’ is an integer, drop this exact number from bottom/top of sorted set

    - if ‘lower’/’upper’ is fractional (0 < x < 1), drop this fraction from bottom/top of sorted set

    - fraction rounds down to nearest whole number of items

    - if include is true then include Lower <= x <= Upper otherwise exclude this range

    - the List parameter is created from a CubeRange/PeriodToDateCubeRange Expression function

Example

To find the max of Destinations after removing the top-most / bottom-most value:

  1. Create a Cube and add Destination as a dimension

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

CountList

This function allows you to count the number of elements in a given list.

Example

To create a simple count list, first create the following Expression:

- gives a value of 2

Example

To count the number of values on a Cube dimension with a value below 100,000:

  1. Create a Cube and add Destination as a dimension

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

CreateList

This function allows you to create a list object from a set of values or lists. The return type of the function is a list and it therefore needs to be used inside a function that acts upon a list.

Example

To create an average from the horizontal and vertical slice through a Cube’s cell:

  1. Create a Cube with a dimension of Destination (vertical) and Gender (horizontal)

  2. Create the following Expression:

  1. Drag the Expression into the middle of your Cube and build:

ListContains

This function allows you to test whether a given list contains one of a number of values. The return value is 0 if none of the test values are matched or, otherwise, the index of the matched value (i.e if it matches 1st value = 1, 2nd value = 2, etc).

Example

To create a simple ListContains example:

- returns 2

Example

Based on a Cube, the following Expression returns 1 if the people in that cell have been to all 20 destinations between them, or 0 otherwise:

  1. Create a Cube with a dimension of Income (vertical) and Occupation (horizontal)

  2. Create the following Expression:

  1. Right-drag the Destination variable into the middle of the Cube and add Count Distinct(Destination) as a statistical measure

  2. Set the Omit Zero Axes property off

  1. Drag the Expression into the middle of your Cube and build:

Other list functions include:

TextListContains

This function searches the text list for the values String1, String2, etc. and returns the index of the first match found. Returns 0 if no match found.

StrList

This function turns a numeric or text list into a delimited string. An optional delimiter can be specified in a second parameter.

CreateTextList

This function creates a text list. The values in the list are either strings or text lists. This function must then be used as a parameter in a function that takes a text list.

For example:

Results in an output of:

 

Added in Q1 2024

TextListMapping

This function returns a text list. The values in the input text list each have a mapping function applied to them. This function is referred to by a name and options include, for example, mapping postal sector and district to the usual FastStats variable formats, converting the values of an input file to all upper case, or all lower case.

See Expressions: Mapping options for List and File functions for a complete list of the mapping options.

 

Back to Expressions: Overview