Combining Table Results in Orbit using a CubeLookup Expression

Combining Table Results in Orbit using a CubeLookup Expression

Sometimes it is useful to be able to get results from a Table in Orbit and use them in another Table. Within Orbit, it isn't currently possible to refer to results in one tile within another one.

However, we can use a combination of some powerful Expression functions and Virtual Variables in FastStats in order to make this possible.

As an example, the following Dashboard features two Table tiles. They both have the Occupation variable as the row dimension.

Let's say we wanted to get the column values from Table Two as another measure in Table One:


To do this, we need to log into the same system using FastStats and we need to create the Table Two example from within FastStats:



We can use the CubeLookup function in order to get at the values for each Occupation category. To do this:

Click the Expression tool
Drag and drop the Cube onto the Expression window
This will add the Cube as a CubeLookup() function, similar to this:



In general, the CubeLookup function has the syntax:

CubeLookup({Cube Name},[Measure Number], [Dimension Name])

If you click the CubeLookup tab then you can choose which Dimension and which Measure to reference. In the example above, there is only one measure which is People and there is only one Dimension which is Occupation, so we do not need to specify any of these two parameters.

What the above function does is look up the values in the row dimension (Occupation) and look up the value in the People measure for that value.

In this additional example below, we have two measures. If we wanted to return the Sum(Cost) measure:



We could click the CubeLookup tab, uncheck the Default Measure option and choose Sum(Cost) from the Measure listbox control:



which would produce the following Expression:

CubeLookup({Cube #1 - Table Three},2)

where the ',2' is referring to 2nd measure i.e. Sum(Cost).

Returning to our example, which only has a single measure, we need to click the button shown in red below in order to create a virtual variable from this CubeLookup function:



If we create a virtual variable then what this effectively does is record the value in the Cube for every record with that particular category. So, if a person has 'Manual Worker' as an Occupation then we will store 119 in their record. If they have an Occupation of 'Director' then we will store 60, and so on. We need to proceed through the virtual variable wizard in order to create the variable. In this example, we can produce an integer value as the number of People is a whole number. I named this 'Table Two Measure'.

Now that we have this as a numeric variable then we can add it to any other Table, Cube or any other Tile in the same way as any other variable. The key though is to add this as a measure in the left-hand Table but specify a function which only returns a single value; if we chose the default function of 'Sum' then Orbit would total up all of the values for that category whereas we want a single value. Therefore we need to choose a function like Mean, Minimum or Maximum to restrict it to just a single value per category.

To add this measure to the Table on the left-hand side, edit the Dashboard and edit that Tile:

Click the +Measure option
Search for the virtual variable you created earlier (in my example it was named 'Table Two Measure')
Set the Function to be Mean (or Minimum or Maximum)



and click Apply

The People value from Table Two is now available in Table One:


One thing to note is that this value is static. In the example below, I have filtered the dashboard so that there is only a single record showing in Table Two but the original values are being shown in the Mean(Table Two Measure) column within Table One:


This is because the values in the virtual variable are loaded based on the Cube at the time of the data load. When the data refreshes then the Cube values will change and the VV will update accordingly but it will not be affected by any filters applied to the dashboard.