Skip to main content

Klips: Modelling data sources - Writing formulas using results references

Updated

You can use results references when modelling data sources. Results references enable you to refer to columns within the modeller after date properties and formulas are applied to them. To use a results reference, type & into the formula bar and select from the list that displays.

This article includes the following examples of using results references when modelling data sources for custom Klips:

Adding column data together to create a total column

In the modeller, 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 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 the 3-dot menu beside Data Sources and select Data Source Library.
    If you don’t see this option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.
  2. Select the desired modelled data source.
  3. On the About this Modelled Data Source page, click Edit Model.
  4. 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.
  5. Click in the new column’s header and enter a new name “Q1 Sales”.
  6. With the new column selected, enter the formula (&Jan Sales+&Feb Sales+Mar Sales) into the formula bar, and click Enter.

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

Consolidating related data

When modelling a data source, 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 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.
    If you don’t see this option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.
  2. Select the desired modelled data source.
  3. On the About this Modelled Data Source page, click Edit Model.
  4. 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.
  5. Click in the new column’s header and enter a new name “Name and ID Number”.
  6. With the new column selected, enter the formula CONCAT(&Name," ",&ID Number) into the formula bar, and click Enter.

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

Calculating the percentage of change

The following example describes how to use results references to calculate the percentage of change between two periods of time. 

The data source to be modelled includes separate columns for sales in January and February:

In this example, a column is added to the data source that includes the percentage of change between sales in January and sales in February.

To calculate the percentage of change:

  1. In the left navigation sidebar, click Data Sources and select the modelled data source you want to open.
    If you don’t see this option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.
  2. Select the desired modelled data source.
  3. On the About this Modelled Data Source page, click Edit Model.
  4. 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.
  5. Click in the new column’s header and enter a new name “% Change”.
  6. With the new column selected, enter the formula (&February Sales-&January Sales)/&January Sales into the formula bar, and click Enter.

    Your new column is populated with the percentage of change data, according to the formula you entered.
  7. Click Save.

Related articles

Related articles