Calculate Expression wizard - selector output example

There are two established methods for creating selector virtual variables from expressions. Firstly, there are a limited number of expression functions where the allowable values can be derived directly from the expression text. Secondly, you can use the Create from Cube wizard to turn a sparse text cube into a selector virtual variable based on a number of codes or a threshold value.

From the Q2 24 software release, you can also create selector type virtual variables direct from expressions using new options within the Calculate Expression wizard:

MANUAL CODES: You can user define the number of codes that the virtual variable has and then specify the codes and descriptions. The result of the expression is checked against the code and/or description and matched. If there is no match, the value is placed into the unclassified category.
FROM EXISTING VARIABLE: You can specify that the expression takes its codes and descriptions from an existing variable. This is useful for those cases where the known values always match existing ones and you want the values in the same order.

Let's consider an example in practice:

The first stage of this process is to create an expression that you then want to derive a selector virtual variable from. The on the fly pattern match aggregation expression below has been created to identify the destination that represents the longest sequence of holidays a person has made to the same destination (see Expressions: Aggregations on the fly - Pattern Match Longest Sequence for more information).

To generate a selector virtual variable for Destination, from the Virtual Variable Wizards panel within the Wizards ribbon bar:

Click to launch the Calculate Expression wizard.
Start - drag your expression onto the drop-box, or right click and browse to the saved expression. Click Next.
Selection – leave blank so the whole universe is used. Click Next.
Type – set the data type as selector. Click Next.
Selector Type - this is where you determine how to define the codes and descriptions for your virtual variable. The system automatically detects if the information can be derived from the expression text and, if not, this option is inaccessible. Otherwise, the two new options remain for you to select from and, in this example, choose From an Existing Variable and then drag and drop the Destination variable onto the drop-box:

Click Next.
Dynamic Variable - because the expression we are deriving the variable from contains an aggregation, it is not possible to create a dynamic virtual variable here and this option is inaccessible. Click Next.

The remaining steps are standard for wizards and allow you to choose the System folder for the new variable, add notes, name the variable and set security options for its use. Once complete, the new virtual variable is available to use for selection,on a cube, etc. The categories and codes, and their order are the same as the Destination variable that was used as the source.