Expressions: Derived Cube List Functions

To enable the creation of Derived Cube Expressions, the following List functions are available:

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:

  • Create a Cube and add Income as a dimension

  • Create the following Expression:

  • 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:

  • Create a Cube and add Income as a dimension

  • Create the following Expression:

  • 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:

  • Create a Cube and add Destination as a dimension

  • Create the following Expression:

  • 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:

  • Create a Cube and add Destination as a dimension

  • Create the following Expression:

  • 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:

  • Create a Cube and add Destination as a dimension

  • Create the following Expression:

  • 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:

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

  • Create the following Expression:

  • 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:

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

  • Create the following Expression:

ListContains(CubeRange([cube$0], "Count Distinct(Destination)", "|0.Income", 0), 20)

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

  • Set the Omit Zero Axes property off

  • Drag the Expression into the middle of your Cube and build:

 

Click to return to Expressions: Derived Cube