Advanced Formula Editor

An introduction to using the advanced formula editor and its syntax.

Last updated: Sept. 1, 2022

The advanced formula editor allows you to create very customizable formulas. We call it "advanced," but it's actually not that hard to work with. It uses Excel-like syntax, therefore, if you are familiar with Excel, it should be rather easy for you to write a formula in the advanced formula editor.

  1. To create a formula in the advanced editor, go to KPI Management > Formulas > + New formula in Plecto.
  2. Click Switch to advanced editor in the top-right corner. You can now start building the formula.

If you have built a formula in the visual editor, you can switch to the advanced editor, and your formula will be shown in the advanced editor's format.

Be aware of the limitations

The visual editor is currently a BETA feature and doesn't support IF statements just yet. Once you use certain functions or modifiers in your advanced formula, it might not be possible to see the formula in the visual editor. This applies to IF statements and advanced calculations.

If you want to calculate the total value of your won sales deals, you could create a formula that looks like the following:

(
    Sum(Pipedrive Deals,Status="Won",Value).Date(Updated date)
)

Here's a breakdown of what each of the elements represents:

  • Sum – indicates what data function is used.
  • Pipedrive Deals – the name of the data source used in this formula.
  • Status – this is a field name in the Pipedrive Deals data source. In this case, it works as a filter because it filters out the registrations where the status is "Won."
  • Value – another field name in Pipedrive Deals. This field contains the value of the sales deals that will get summed.
  • .Date – date modifier. Read more here.
  • Updated date – the date field in Pipedrive Deals that determines which registrations Plecto should take into consideration when calculating the sum for today, the current week, or other time periods.

The advanced formula editor uses an excel-like syntax to write a formula. Each formula must be enclosed in parentheses:

(
     FORMULA HERE
)

To start building your formula, choose one of the following functions of how you want to transform the data: Sum(), Count(), Avg(), Min(), Max(), First(), Last().

More about data functions

Visit this article to read more about each function: Data functions. Or click here to find more articles on IF, AND, OR statements and different modifiers.

  • The following example uses the Sum() function to display the value of all the deals in a data source. You can copy this formula and use it in your own advanced editor. Make sure to replace "Deals" with the name of your data source, and "Value" with the name of the column you want to sum. 
(
    Sum(Deals, Value)
)
  • You can also add, subtract, multiply, or divide data from two different data sources together in a single formula. This is done by adding an operator (such as ➕ or ➖, ➗, ✖️) to the formula followed by another function. The following example adds all values from the data source Deals together with all amounts from the data source Opportunities.
(
    Sum(Deals,Value) 
    + 
    Sum(Opportunities,Amount)
)
Adding filters
  • The advanced editor gives you the option to add as many filters to each function as you like. To add a filter to a function, insert the following into your formula:

,Field_name="Value",

Replace Field_name and Value with the name of the field and data you wish to filter for. For example, if you want to see all the deals from a data source called Deals that are in the stage "Waiting on contract," then the formula would look like this:

(
    Sum(Deals,Stage="Waiting on contract",Value)
)
  • You can also filter for registrations that are not equal to a set value. To do so, replace "=" with "!=". For example, to see all the deals in the data source Deals that are not in the stage "Waiting on contract," you would see a formula like the following:
(
    Sum(Deals,Stage!="Waiting on contract",Value)
)
  • If you want to show data from two different stages, you can create two functions in the same formula by adding them together. The formula will sum the values for all deals in the data source Deals that are in the "Waiting on contract" stage and in the "Commit" stage:
(
    Sum(Deals,Stage="Waiting on contract",Value)
    +
    Sum(Deals,Stage="Commit",Value)
)
Adding multiple filters

You can add multiple filters to a single function by separating each filter with a &. This can be done to each function as many times as you want:

(
    Sum(Deals,Stage="Waiting on contract"&Status="Open"&Company="Plecto",Value)
)

Please see the following articles for more details on how to create more advanced formulas using IF statements, modifiers, and much more in the advanced formula editor:

IF statements

Date & History modifiers

Month-to-date target

You can also add a prefix or a suffix when using the advanced formula editor. The prefix should be added before the first parenthesis in the formula, and the suffix should be added after the last parenthesis.

  • An example of a prefix. This will output the value of all deals in the stage "Waiting on contract" with a "$" prefix in front of the value, e.g., "$ 10.000".
$ (
    Sum(Deals,Stage="Waiting on contract", Value)
)
  • An example of a suffix. This would output the number of deals in the stage "Waiting on contract" with "Deals" suffix after the value, e.g., "5 Deals".
(
    Count(Deals,Stage="Waiting on contract")
) Deals

Was this article helpful?

Please leave a comment to help us improve.