Advanced Formula Editor

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

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

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:

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

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

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

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:

• 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:

• 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:

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:

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

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