Expressions: Derived Cube - CubeRange

This function allows you to identify a range of numeric values for a dimension. Because a Cube can only return a single numeric value, the CubeRange function cannot be the outermost in the Expression and is used in conjunction with other functions, such as SUM, MEAN, MIN, MAX, that can do calculations on lists.

You can repeat the above steps with the following Expressions to see the effect of different relative cell information:

Max(CubeRange([cube$0], “People”, “+0.Banded Booking Date (Years)”, 0))

  • all the cells up to the current one

Max(CubeRange([cube$0], “People”, “-1. Banded Booking Date (Years)”, 0))

  • all the cells up to the previous one

Max(CubeRange([cube$0], "People", "3. Banded Booking Date (Years)", 0))

  • all the cells up to cell 3

Max(CubeRange([cube$0], “People”, “|0.Banded Booking Date (Years)”, 0))

  • all the cells on this dimension

Max(CubeRange([cube$0], “People”, “-2. Banded Booking Date (Years)”, 3))

  • from the current cell, go back 2 cells and create a list of the following 3 cells

    e.g. for 2017 in the above Cube this equates to moving back to 2015 and creating a list that includes 2015, 2016 and 2017

Whilst most typically the CubeRange function will correspond to a set of cells across a single dimension, you can use it to create a 2 (or more) dimensional range by using multiple dimensions in the function.

For example, in a 2-dimensional Cube display of Gender and Region, the following Expression allows you to identify the highest value cell across both dimensions displayed – in this example a count of 138,796 representing Females from the South East (Outside M25) Region:

Or, to find the highest count of people across each category row and column:

Note

CubeRangeFunction - CubeRange (cube$,m,d1,d1size,d2,d2size ...) - selects the cell values from the finished Cube into a list

Measure m referenced by the Cube dimension coordinates - define contiguous ranges d1,d1size,d2,d2size

Dimension coordinates are 1-based and passed as strings

x = category x (absolute reference), "1"=first, "2"=second, ..., "L"=last

0 = marginal total

+x = current category +x - relative reference to offset cell

-x = Current category -x - relative reference to offset cell

+0 = current category - relative reference to this cell

|0 = this whole row along this dimension, size is set to number of cells

Dimension sizes are parsed as numbers - e.g. 2 = 2 cells, 0 = all cells relative to the dimension coordinate

It is also possible to use this function by not naming the dimensions, meaning that only the ‘cell’ part is used. The cells are referenced to dimensions in the order in which they appear in the Cube – with vertical dimensions first and then horizontal ones. If you change the Cube dimensions and rebuild, the results reflect the new dimensions. Whilst the naming strategy approach is most common, the positional approach can be useful when creating Cube templates.

 

Click to return to Expressions: Derived Cube