Introduction to Formulas
An introduction to using the simple formula editor and how to create formulas in a matter of seconds.
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:
The data sources contain multiple registrations with individual values that formulas can put together to calculate a result to be displayed 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:
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" -> "Formulas" in the sidebar of the Plecto interface.
From this page, you can create a formula by following these steps:
- Click "New formula" in the top-right corner
- Select the "Simple editor" (read more about the advanced editor here)
- Give your formula a title that describes its purpose that also allows you to keep the list of formulas organized and well-structured
- Click on the "+"-symbol on the grey button to add a function
- Select the function you would like to use in the formula (read more about what the different functions do below)
- Select the data source you would like to use data from, and the fields you would like to sum, subtract, etc., if any
- Press "Save" at the bottom-right of the page
- That is it! You have now created a formula and can apply it to your dashboard
The below recording 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:
You can use the following functions when creating a formula:
- Quantity > Counts the number of registrations in a data source
- Unique values > Counts the numbers 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
You can read more about the available functions in this support article: Lookup Functions.
A formula can be formatted in multiple different ways, which will alter the way the formula is calculated and displayed on your dashboard.
You can change the formatting when creating or editing a formula under "Number formatting".
It is also possible to change the number of decimals the formula should display on the dashboard from the same place.
Using the format "Text", you could, for example, display the title of the latest won deal:
The same could be done using the date-format, where you select the "Won date" instead of the title, from which the formula will output the date of the latest won deal.
You can easily add filters to your functions to make sure you only include the data you wish to use. This is done in by clicking on "Add filter", from which you can choose the field to filter on and its value.
The filtering is case-sensitive, so you should make sure you type the filters exactly the same way as the values are displayed in the selected data source.
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.
Tip: If you would like to filter to see registrations where there is no data in the column. Then you choose the column add = and leave the other box empty.
E.g. You would like to see all your appointments that have not got a price yet.
If you would like to create an "OR" filter, where you, for example, filter by all deals that are won or lost, then you should create one function for the lost deals, and then one from the won deals, from which you add them together:
You can add, subtract, divide, or multiply functions together in the formula editor. This allows you to make both simple, but also very complex calculations depending on your needs.
Time Periods in Formulas
You would usually not define what time period, you would like to view data for, directly in the formula. This is done on the widget, where you will use the formula.
This is done so you can use the same formula on multiple widgets that could be using different time periods. You can then avoid having to create one formula for each time period.
You can add a formula to a widget by doing the following:
- Go to the dashboard you would like to add the formula on
- Add a widget
- Select the formula you would like to use
- Click on "Time period" in the settings of the widget, and select the time period you would like to use on the widget
- Click "Save"
This is also shown in the recording below:
You can use the below formula examples for inspiration on how to create formulas. Please do not hesitate to reach out to our great support team if you should 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.
This is an example on how you can create filters where you specify that a field should be either xx or yy.
This formula will return the number of Intercom Conversations where the "State" is either "open" or "snoozed":
This formula is an example of how you can 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.
This formula is an example of how you can filter so you will see registrations that are blank/have no data in a certain field. Alternatively you can do this if you want to see the registrations that are not blank, so definitely have data in the field.
E.g. You have a field called "Date closed" - You can use this if you want to see all the registrations that do not have a date in this field, by filtering for registrations where Date closed is empty. So all the active, open deals will be shown.
Closed date is empty.
E.g. You have a field called "Date closed" - You can use this if you want to see all the registrations that do have a date in this field, by filtering for registrations where Date closed is not empty. This is good as you do not want to specify a specific date, just that there is a date. This will show you all the closed deals.
Closed date is not empty (this means it is filled)