Introduction to Advanced Formulas

An introduction to using the advanced formula editor and it's syntax.

The advanced formula editor allows you to make much more complex calculations compared to the simple editor.

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.

 

Screenshot 2019-09-16 at 16.38.10

This will redirect you to the advanced formula editor, from where you can begin to do changes to your formula.

Please note that 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:

Example of an advanced formula

"Sum" (light green): Indicates what function is being 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".

Syntax

The advanced formula editor uses a syntax similar to Excel formulas.

Each formula must be enclosed by a parenthesis:

(
FORMULA HERE
)

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:

(
Sum(Deals,Value)
)

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, multiple or divide data from two different data sources together in a single formula. This is done by simply 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".

Adding filters

It is possible to add three filters for 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:

,Column_name="Value",

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)
+
Sum(Deals,Stage="Commit",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:

IF-statements

Date & History modifiers

Month to Date Targets

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")
) Deals

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".