Introduction to Advanced Formulas
An introduction to using the advanced formula editor and its syntax.
Creating an Advanced Formula
You can access the advanced formula editor when viewing all formulas from the menu "KPI management" in the sidebar of Plecto's frontpage. This is done by accessing the formula you'd like to edit and clicking "Advanced editor" in the top right corner.
This will redirect you to the advanced formula editor, from where you can begin to make changes to your formula.
Please 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.
It is also possible to use the advanced formula editor directly from the dashboard-editor when using some of the functions or modifiers mentioned above. By default, Plecto will always be using the simple editor when possible.
An Example of an Advanced Formula
An advanced formula could look like this:
"Sum" (light green): Indicates what function is used. See the list below for more information on the functions available.
"Pipedrive Deals" (dark green): Indicates what data source to use in the function.
"Status" (purple): A filter in your function. See below for more information on how to use filters.
"Value" (blue): The column in the data source that you wish to sum. This can also be changed to any column formatted as a decimal number in your data source - when using the function "sum."
The advanced formula editor uses a syntax similar to Excel formulas.
A parenthesis must enclose each formula:
Now you can use one of the following functions to describe how you would like to transform your data:
Sum(), Count(), Avg(), Min(), Max(), First(), Last()
Please visit the following article for more in-depth details about each function: Lookup functions.
The following example will use the "Sum()"-function to display the value of all deals in a data source:
Replace "Deals" with the name of the 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 merely adding an operation (e.g., "+") to the formula followed by another function:
Sum(Deals,Value) + Sum(Opportunities,Amount)
This example will be adding all values from the data source "Deals" together with all amounts from the data source "Opportunities."
It is possible to add three filters to each function when using the simple formula editor. This can sometimes be too few, which is why the advanced formula editor allows you to add as many filters as you would like to each function.
This is done by adding the following to a function:
Replace "Column_name" and "Value" with the name of the column and data you wish to filter for.
The following example will output the value of all deals in the data source "Deals" in the stage "Waiting on contract":
Sum(Deals,Stage="Waiting on contract",Value)
You can also filter for registrations that are not equal to a set value by replacing "=" with "!=":
Sum(Deals,Stage!="Waiting on contract",Value)
This will output the value of all deals in the data source "Deals" that are not in the stage "Waiting on contract."
If you want to show data from two different stages only, you can create two functions in the same formula adding these together:
Sum(Deals,Stage="Waiting on contract",Value)
This 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.
Adding Multiple Filters
You can add multiple filters for a single function by separating each filter with a "&":
Sum(Deals,Stage="Waiting on contract"&Status="Open",Value)
This can be done as many times as wanted for each function:
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:
Prefix and suffixes
You can also add a prefix or 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.
Adding a suffix to a formula would be done like this:
Count(Deals,Stage="Waiting on contract")
This would output the number of deals in the stage "Waiting on contract" followed by the text "Deals", e.g., "5 Deals".
An example of adding a prefix to a formula:
Sum(Deals,Stage="Waiting on contract", Value)
This would output the value of all deals in the stage "Waiting on contract" with "$" in front of it, e.g. "$10.000".