How to Build a Formula in the New Editor

All formulas are built in the formula editor in Plecto. Here's how to create a new formula:

1. Go to Formulas > + New formula.
2. Give your formula a clear title that best describes its purpose and is easy to find in your KPI list.
3. Now you can start adding components and filters, apply date and history modifiers, choose the number format, add conditional colors, and more.

Components are the fundamental parts of your formulas. A component can be, for example, a data function (number of, the sum of, minimum, maximum, average, and more), an existing formula, group, date function, or a static number. You can add an indefinite number of components to your formulas. Click + New component and select one of the options.

Data functions

The data functions allow you to create a wide range of calculations made based on the data in your data sources. A data function searches for information within the registrations and fields in your data source, and the type of data function determines how Plecto handles the calculations. Read more about data functions in Plecto.

Existing formula

This component allows you to use one of your existing formulas as part of the current formula. Adding an existing formula as a component will replicate that formula but will in no way modify its original structure. However, if you change the existing formula, this component will also change in all the other formulas where it is used.

You can preview the existing formula right in the formula editor. Add a new existing formula component, open the dropdown list, hover over a formula, and click Preview formula on its right. A slide-in on the right will appear where you can preview the existing formula.

Group

Grouping works just like parentheses. To create a group, add a new group component or drag and drop independent components onto each other to group them. Similarly, grab and drag the components out of the group to ungroup them. You can also place a group within a group to create nested parentheses.

Date function

This component counts the number of days, workdays, or holidays within the time period you choose on the widget. You can use it to calculate daily targets or any other metrics where you need to consider the number of days, workdays, or holidays in the calculations.

For example, selecting Workdays from Widget start date to Widget end date will make the formula return 5 if you set the time period to the Current week. If you have a weekly target that you divide by this number, you will obtain a daily target value.

Similarly, if you select Days from Widget start date to Widget end date and set the time period to the Current month, the formula will return 30, 31, 28, or 29, depending on what the current month is.

Static number

This component allows you to add a fixed number to the formula.

Once you have created a component using a data function, you can easily add filters to make Plecto look at specific data in your data source.

1. Click + Add filter and choose the field you want to filter.
2. Select a field.
3. Type in the filtering condition (applies to all comparators except is empty and has any value).
4. Click Done to finish and save the filter.

All components based on data functions allow you to apply date and history modifiers. Read the full article: Date & History Modifiers.

The date modifier makes the component look for information in your data source based on another date field than the default date. For example, if the default date in a data source is Created date, you can use a date modifier to make the formula look at data based on the Closed date field instead.

Here's how to add a date modifier:

1. Click the three little dots in the component's top-right corner.
2. Click Date field. You will see a list of all the available date fields in the data source, including the default date.
3. Select any of the fields from the list to change the date used in your component.

The history modifier manipulates the formula to include data from a different time period outside of what you can select on widgets, reports, notifications, or other features where formulas are utilized.

Here's how to add a history modifier:

1. Click the three little dots in the component's top-right corner.
2. Click Add history modifier.
3. Choose an offset period such as 5 days, 2 months, or others.
4. Click Done to finish and add the modifier to the component.

A formula result can be formatted in multiple ways, which will change the way it is displayed on your dashboards and reports. You can change the number format in the formula editor. The available formatting options are text, decimal number, Yes/No, percent, duration, date and time, date, and time since. See the different number formatting outputs in the video below.

• Text – Display the latest completed tasks, campaign names, or any other text that is part of your data.
• Decimal number – Display a number value.
  • With this option, you can select the number of digits that appear after the decimal point.
  • You can also reverse the trend indicator (the small triangle on the widget that appears green or red whenever a value increases or decreases) to recognize lower values as the preferred outcome. For example, 3 minutes of the average call waiting time is better than 5 minutes.
• Yes / No – The formula will return "No" if the result is 0 or "Yes" if the result is 1 or higher.
• Percent – Display values as a percentage. Here, you also can select the number of digits that appear after the decimal point or choose to reverse the trend indicator.
• Duration – Show the duration of your activities. If you have a column in your data source that shows the duration of an activity in seconds, this formatting option will make sure to convert your data into a clear duration output. For example, 7 days 10:35:12.
• Date and time – Display the date and time of your latest closed deals, average resolution time, or any other date and time from a date field in your data source.
• Date – Display the date of your upcoming billing, latest closed deals, or any other date from a date field in your data source.
• Time since – Display the running time since an activity started. The time counting starts from the date and time that is returned from the formula.

Here are some basic KPI examples that you can use in your organization. For more inspiration, visit our KPI Inspiration page.

Conversion Rate (%)

In this example, the formula divides the number of won deals by the total number of deals to return the conversion rate of won deals.

1. To create this formula, add the first component that counts the number of [data source name], and add a filter to the component to specifically count the registrations with the status "won."
2. Add another component that counts the number of [data source name]. This component will count all the registrations in your data source.
3. Change the operator to ➗.
4. Set the number format to Percent.
5. To finish, click Save in the bottom-right corner.

Revenue

This example shows how to create a formula to calculate revenue based on data from different data sources. Create a new component for each data source you want to get data from and apply filters if necessary.

Blank filters

This example shows how to create a formula that counts blank registrations (that have no data) in a specific field. This could be useful to obtain, for example, the number of ongoing calls or deals that don't have an estimated value yet.

All you have to do is create a component that counts the number of [data source name] and add a filter where the comparator is "is empty."

Alternatively, you can select the comparator "has any value" to see all the registrations that are not blank (have data) in the field. The formula will then filter and include registrations that are not empty. Let's say you have a data source that contains the information of your sales deals, and you want to see the number of deals that have a close date in the data source.