Writing formulas using results references

Results references enable you to refer to columns, series, and other sub-components after actions, date properties, and formulas are applied to them. You can use results references in the formula bar of the Klip Editor and when modelling a data source.

Results references are most commonly used with actions and date properties to leverage displayed data or results of the actions and date properties in other calculations.

To use a results reference, type & into the formula bar and select from the list that displays.

Note: Results references refer to displayed data. Formula references refer to formulas before actions or date properties are applied. Learn more about formula references here.

Watch the video below for an example of using results references in the Klip Editor.

This article includes some examples of how you can use results references when writing formulas:

Note: If the data source you are modelling is going to be used for a PowerMetric and the PowerMetric will include a rate or percentage calculation (for example, Lifetime Value to Cost of Acquisition ratio), due to differences in aggregation methods for calculations, you should perform those calculations by building a new, Calculated PowerMetric instead of using results references in the modeller.

Calculating the percentage of change

Results references can be used to simplify calculations. The following example describes how to use results references to calculate the percentage of change between two periods of time. A column is added to the table to show the percentage of change between the previous month’s sessions and the current month’s sessions.

The Klip below shows total sessions, from different mediums, by month:

 

In this example, a column is added to the Klip to show the percentage of change between the sessions in the previous month and the current month.

To calculate the percentage of change:

  1. In your dashboard, click the three-dot menu on your Klip and select Edit to enter the Klip Editor.
  2. In the component tree, right-click the Table component, and click Add Column.
  3. Set the Format as option to Percentage.
  4. Select the Data tab and, in the formula bar, perform a percentage of change calculation using results references, as in the example below.

  1. Click Save.

Aligning data from two data sources

You can align data from two data sources using the LOOKUP function. Using results references in the LOOKUP formula enables you to reference columns after actions (for example, filtering and grouping) have been applied to them and maintain the results in further calculations.

The Klip below shows data for campaigns, clicks, and sessions from one data source:

In this example, a column with calls data is added from a second data source using the LOOKUP function. The formula uses a results reference to ensure the actions applied to the data for the first data source are also applied to the calls data from the second data source.

To align data using LOOKUP and a results reference:

  1. In your dashboard, click the three-dot menu on your Klip and select Edit to enter the Klip Editor.
  2. In the component tree, right-click the Table component, and click Add Column.
  3. Assign the Calls data to the fourth column in the table.
  4. In the Calls column, use the LOOKUP function to ensure that the calls from the second data source are aligned with the rest of the data, as shown in the example below.

  1. Click Save.

Note: The previous example applies to building Klips using the Klip Editor. You can also align data from more than one data source when modelling a data source, by joining them using the merge tool. Learn more about merging modelled data sources here.

Consolidating related data

When modelling a data source that you will use to build a PowerMetric or a Klip, you can use the CONCAT function and a results reference in your formula to consolidate related data from separate columns into a single column.

The data source to be modelled (as shown below) includes separate columns for sales rep names and sales rep IDs:

The following example uses the CONCAT function and a results reference to combine sales rep names (from one column) and sales rep ID numbers (from a different column) into a single, consolidated column that includes the sales rep names alongside their ID numbers.

To consolidate data using CONCAT and a results reference:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.
  2. On the About this Modelled Data Source page, click Edit Model.
  3. Click the + column. (It displays after the last column in your modelled data source.)

This adds a new, empty column and opens the formula bar.

  1. Click in the new column’s header and enter a new name “Name and ID Number”.
  2. With the new column selected, enter the formula (as shown below) into the formula bar, and click Enter.

Your new column is populated with the consolidated data, according to the formula you entered.

  1. Click Save.

Adding column data together to create a total column

When modelling a data source that you will use to build a PowerMetric or a Klip, you can use simple math (addition) and results references in your formula to add columns of data together to create a single, totalled column.

The data source to be modelled (as shown below) includes separate columns for sales in January, February, and March:

The following example uses simple math (addition) and results references to add three columns of data together to create a single totalled column.

To add data from separate columns together into one totalled column:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.
  2. On the About this Modelled Data Source page, click Edit Model.
  3. Click the + column. (It displays after the last column in your modelled data source.)

This adds a new, empty column and opens the formula bar.

  1. Click in the new column’s header and enter a new name “Q1 Sales”.
  2. With the new column selected, enter the formula (as shown below) into the formula bar, and click Enter.

Your new column is populated with the consolidated data, according to the formula you entered.

  1. Click Save.

Related articles

Have more questions? Submit a request