Introduction to Advanced Formulas

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

Last updated: April 19, 2021

Creating an Advanced Formula

The Advanced formula editor is great for when you need to create a more elaborate formula and add different filters or modifiers to it. The Advanced editor 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.

To create an advanced formula, go to "KPI Management", then "Formulas" in Plecto. Click on "+ New formula" and select the Advanced editor. You can now start building the formula.

You can also transform an existing simple formula by switching from the Simple formula editor to the Advanced editor. To do so, open your formula, then click on "Switch to Advanced editor" at the top-right corner. It will redirect you to the Advanced editor from where you can begin to make changes.

Note: Once you use certain functions or modifiers in your formula, it will no longer be possible to view the formula in the simple editor. This applies to date- and history modifiers, IF statements, and advanced calculations.

An Example of an Advanced Formula

An advanced formula could look like this:

advanced-formula-2.webp

"Sum" (light green): Indicates what function is used.

"Pipedrive Deals" (dark green): Indicates what data source is being used in the function.

"Status" (purple): Indicates the filter in your function. In this example, the status is set to "Won".

"Value" (blue): The column in the data source that you wish to sum. You can "sum" any column that is formatted as a decimal number.

Syntax

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().

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

The following example uses "Sum()" function to display the value of all the deals in a data source:

(
Sum(Deals,Value)
)

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 wish to sum. 

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 operation (e.g., "+") 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 Simple formula editor allows you to add up to three filters to each function. However, it can sometimes be too few, which is why 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:

,Column_name="Value",

Replace "Column_name" and "Value" with the name of the column 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)
)

Functions and Modifiers

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 Targets

Prefix and suffix

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?