Calculate Expression wizard - numeric output example

The example below demonstrates how you can use the Calculate Expression wizard to create a virtual variable that allows you to search on a person’s average holiday spend, calculated from an expression.

The first stage of this process is to create the expression that calculates average holiday spend.

Open the Expression tool and create and name the expression shown below.

The Total Spend on Bookings and Total Number of Holidays variables used have been pre-created using the Aggregation wizard.

Next from the Virtual Variable Wizards panel within the Wizards ribbon bar:

Click to launch the Calculate Expression wizard.

Start - drag your Average Holiday Spend 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 numeric and adjust the number format and decimal place settings, if relevant. Here - leave as the defaults. Click Next.
Dynamic Variable – Select Dynamic as the means to generate the variable. Click Next.
Folder - for this example, leave this as the default Others folder. Click Next.
Notes - for example, "Created by ... for ...". Click Next.
Name - enter your description for this variable - e.g. Average Holiday Spend. Click Next.

Always consider company and best practice for naming conventions - for example, add your initials to identify yourself as the creator of this variable.

Finish – as a dynamic variable, the number of updated records is not displayed here but you can see the created numeric variable highlighted in the Others folder of the System explorer.
Tick the Show new variable as a selection box. Click Finish.

The variable is now displayed within a new selection window, allowing you to enter the value(s) or range(s) of interest.

You could also drag this variable onto a Cube dimension to create a banded breakdown: