Introduction to Formulas

An introduction to using the simple formula editor and how to create formulas in a matter of seconds.

Last updated: June 17, 2021

Note: This article describes how to use the simple formula editor. Please view this article to read the introduction to the advanced formula editor.

Introduction to Formulas

Formulas are an integral part of Plecto. They are the recipes for taking data from data sources and converting it to metrics on dashboards. Their only job is to take registrations from a data source and calculate a result with their values. The widgets on the dashboards then use the formulas and display their result. The relationship between data source, registrations, formulas and dashboards is illustrated in the flowchart below:

intro-formulas-1.png

The data sources contain multiple registrations with individual values that formulas can put together in order to calculate a result that you would like to display on a dashboard.

Formulas are a plain text representation of how a result is calculated, and its text has to follow a strict syntax for Plecto to understand it. The syntax can be thought of as a programming language, similar to what is used in spreadsheets such as Google Sheets or Microsoft Excel.

In a spreadsheet, you usually organize your data in rows and do operations on the row’s columns such as summation or sorting. Plecto works in a very similar way - the registrations are rows in a table and their fields are the columns. The table below represents 3 registrations:

intro-formulas-2.png

A formula generally does the following steps when calculating a result:

  • Choose a data source.
  • Narrow down which registrations to include from that data source.
  • Choose which registration fields to look at.
  • Calculate the result from the values of the registrations’ fields, e.g. sum them.

Creating a Formula

A formula can be created from the formula page in Plecto, which is located under "KPI management", then "Formulas" in the sidebar of the Plecto interface.

From this page, you can create a formula by following these steps:

  1. Click "+ New formula" in the top-right corner
  2. Select the "Simple editor" (read more about the Advanced editor here)
  3. Give your formula a clear title that best describes its purpose and that allows you to keep a well-structured list
  4. Click on the "+" symbol under "Formula" to add a function
  5. Select the function you would like to use (read more about functions below)
  6. Select the data source you would like to use data from, and the fields
    you would like to sum, subtract, etc., if any
  7. Make sure to choose the right output format under "Number formatting"
  8. To finish, click "Save" at the bottom-right

That's it - you have now created a formula and can apply it to your dashboard!

Note: The recording below shows how this can be done in a matter of seconds. This example creates a formula that counts the number of HubSpot tickets, but can easily be used for any kind of data:

Functions

You can use the following functions to a formula:

  • Quantity > Counts the number of registrations in a data source
  • Unique values > Counts the number of registrations with a unique value in a field that you specify
  • Sum > Sums the values of a field in a data source
  • First > Returns the first value of a field in a data source based on the date of the registration
  • Last > Returns the last value of a field in a data source based on the date of the registration
  • Min > Returns the lowest value of a field in a data source
  • Max > Returns the highest value of a field in a data source
  • Average > Returns the average of all values in a field you select

Tip: Read more about the available functions in this article: Lookup Functions.

Formatting

A formula can be formatted in multiple ways, which will alter the way the formula is calculated and displayed on your dashboard. You can change formatting when creating or editing a formula under "Number formatting". There you can also change the number of decimals you want the formula to display on the dashboard.

If you use the format "Text", you could, for example, display the title of the latest won deal.

intro-formulas-3.png

An example of a formula where the number formatting is set to "Text" to display the latest won deal.

The same can be done to make the formula output a date format. In that case, instead of the field "Title", you would select another field, for example, "Won date" and adjust the number format to "Date". This will allow the formula to display the date of the latest won deal.

Adding Filters

You can easily add filters to your functions to make sure you only include the data you wish to use. To do this, click on "+ Add filter" and choose the field you want to filter and its value.

The filtering is case-sensitive, so you should make sure to type the filters exactly the same way as the values are displayed in the selected data source.

intro-formulas-4.png

An example of how to add a filter to your formula.

Tip: The simple formula editor allows up to three filters per function. You can use the advanced formula editor to create many more. Read more about this here.

Making Calculations

You can add, subtract, divide, or multiply functions in the formula editor. This allows you to make both simple, but also very complex calculations - depending on your needs.

intro-formulas-6.png

An example of a formula that calculates the division of two functions.

Time Periods in Formulas

You do not define the time period a formula should look at within the formula itself. This is all done on the dashboard and the individual widgets.

It allows the formulas to be reused across dashboards and reports without having to create new formulas for each different time period you want to look at. It means that you can use the same formula on multiple widgets where different widgets display different time periods.

To add a formula to a widget:

  1. Go to the dashboard you would like to add the formula on
  2. Click on "+ Add widget", select one and drag it onto the dashboard
  3. Select the formula you want to use under "Actual Value"
  4. Click on "Time period" under "Options" and select the time period you want to see on the widget
  5. To finish, click "Save"

This is also shown in the recording below:

Example Formulas

You can use the formula examples below to draw inspiration for when you create your own formulas. Please do not hesitate to reach out to our great support team should you have any questions!

Conversion Rate (%)

This formula will divide the number of won deals by the total number of deals to return the conversion rate of won deals. The format of the formula must be set to "Percent" for this formula to work properly.

intro-formulas-7.png

An example of a formula that calculates the conversion rate of won deals.

"OR" Filters

You can create a filter that specifies that a field should be either XX or YY. This filter is useful to, for example, display all the deals that are won or lost. To create an "OR" filter, you need to build one function for each field, respectively - one for Won and one for Lost - and filter them. Then, add them together with the "+" calculation.

intro-formulas-5.png

An example of an "OR" filter that returns data for all won and lost deals.

Revenue

This example shows how to return the revenue from different data sources (integrations, too) in a combined output. You can also add a prefix/suffix to the formula, according to the currency being used.

intro-formulas-9.png

An example of a formula that returns revenue from different data sources.

Blank Filters

This formula is an example of how to create a filter so that you only see the registrations that are blank / have no data in a certain field. For example, you can use this filter if you would like to see all your appointments that don't have a price yet. To do so, click "+ Add filter", select a field - in this case "Price" -, set to "=", and leave the box on the right empty.

Filter out

An example of a formula that filters the field "Price" to display registrations with no data.

Alternatively, you can do this to see all the registrations that are not blank / do have data in the field. The formula will then filter and include registrations that are not empty.

For example, you have a field called "Close date" and you want to look at all the dates when the deals were closed. To do so, click "+ Add filter", select the field "Close date", set to "!=", and leave the box on the right empty. This option is useful for when you do not want to define a specific date, but just that there is a date.

Image 2020-12-11 at 12.42.41 PM.png

An example of a formula that excludes empty registrations for the field "Close date".

Want to learn about Advanced formulas? Read the article here!

Was this article helpful?