Introduction to Formulas
An introduction to using the simple formula editor and how to create formulas in a matter of seconds.
Introduction to formulas
An example of the relationship between a data source, registrations, formulas, and dashboards.
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 MS Excel. Read more about syntax in this article.
Data in spreadsheets is usually organized in rows and performs operations on the rows' columns, such as summation or sorting. Plecto works in the same way, only the rows in Plecto are called registrations, and the columns are called fields.
An example that illustrates 3 registrations.
When calculating a result, a formula generally does the following steps:
- Selects a data source.
- Narrows down which registrations to include from the selected data source.
- Selects which registration fields to look at.
- Calculates the result from the values of the registrations' fields, for example, sums them.
Creating a formula
To create a formula, go to KPI management > Formulas in Plecto. From there:
- Click on "+ New formula" in the top-right corner.
- Give the formula a clear title that describes its purpose and allows you to keep a structured list.
- Click on the "+" symbol under "Formula" to add a function, for example, Quantity or Sum.
- Select the data source you want to use data from and indicate fields, if applicable.
- Choose the output format under "Number formatting," for example, text.
- To finish, click on "Save" in the bottom-right.
Be aware: Please avoid giving your formula the same title as an existing formula or data source in your organization. If two or more titles match, Plecto will display an error message and won't be able to save your formula.
An example of the simple formula editor.
Tip: Add filters to your formulas to create very specific KPIs. You can configure different filter criteria, depending on the fields available in your data sources. For example, display the number of calls with the status "answered," or see the number of registrations from a particular campaign. Read more in the "Adding filters" section below.
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.
Number formatting in Plecto formula editor is equivalent to number formatting in Excel. It can format the result of the formula to decimal numbers, text, percent, date and time, and others. For example, selecting "Decimal number" will make sure to format the result in number values, whereas "Percent" will convert the formula result into a percentage value.
An example of a formula where the number formatting is set to "Text" to display the latest won deal.
From here, you can also select the number of decimals you want the formula to display after a separator, for example, two decimals (3.89) or four (3.8945), or enable "Lower value is better" which will reverse the progress indicator to consider lower values as more favorable, for example, shorter lead time for production or shorter call waiting time for the customer service agents.
Tip: If you use the format "Text," you could, for example, display the title of the latest won deal. Similarly, you can make the formula to output a date format, in which case you would select another field, for example, "Won date" and change the number format to "Date." This will make the formula display 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. 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.
An example of how to add a filter to your formula.
The simple formula editor allows adding up to 3 filters per function. If you want to use more filters, we suggest using the advanced formula editor.
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.
An example of a formula that calculates the division of two functions.
Time periods and formulas
Time periods are not defined within formulas, they are adjusted on widgets on your dashboards and reports. Not defining a time period inside the formula allows it to be reused across dashboards and reports without having to create new formulas for each separate time period you want to look at. You can use the same formula on multiple widgets where different widgets may display different time periods.
To adjust the time period on a widget, simply enable the "Edit" mode on your dashboard, then click "Edit" on the widget. You will see all the available options and settings, including the time period picker. Adjust the time period as needed, then click "Save." See the video below.
Note: The widget allows you to preview the formula result based on the time period you select even before you save the settings. 😊
You can use the formula examples below to draw inspiration for when you create your own formulas. Alternatively, read our KPI Inspiration articles or visit the Plecto Store and install ready-made dashboards for your integrations.
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 as intended.
An example of a formula that calculates the conversion rate of won deals.
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.
An example of an "OR" filter that returns data for all won and lost deals.
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.
An example of a formula that returns revenue from different data sources.
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.
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.
An example of a formula that excludes empty registrations for the field "Close date".