Skip to main content
0%

Advanced Formula Editor

Plecto DocsFeaturesFormulas

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 if you're familiar with Excel-like syntax.

  1. To create a formula in the Advanced editor, go to Formulas > New formula.

  2. Click Switch to advanced editor in the top-right corner. You can now start building the formula.

Formula structure

Here's a formula example and a description of what each component represents:

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

)

  • 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

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

Available functions

Each formula must be enclosed in parentheses. Type the opening parenthesis and choose one of the available functions:

  • Sum: This function sums the values from a particular field. Use it to calculate the won/lost deal values, order values, story points, etc.

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

  • Count: This function counts the number of registrations in a data source. Use it to calculate the number of new leads, the number of completed orders, or how many agents are currently available.

    (
    Count(ServiceTitan,Status="Completed")
    )

  • CountUnique: This function counts the number of unique registrations in a data source. Say you have 30 registrations because you managed to sell 30 units so far, but you only offer 5 different products. The Count function would give you 30, but CountUnique will give you 5.

    (
    CountUnique(Shopify Order lines,Fulfillment status)
    )

  • Avg: This function calculates the average field value of a data source. Use it to get the average deal value, average spending, etc.

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

  • IF: This function allows you to create a calculation based on specific conditions. You can "wrap" your regular formula to make it an IF statement.

    (

    IF(<function>(<data source>,<field>=<filter>)=<target condition>,"<value if true>","<value if false>")

    )

    (
    IF(Sum(Pipedrive Deals,Status="Won",Value)>=50000,"Target Reached 🎉"," ")
    )

  • Min: This function returns the smallest field value of a data source.

  • Max: This function returns the greatest field value of a data source.

  • First: This function returns the earliest field value of a data source.

  • Last: This function returns the most recent field value of a data source.

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

0%
0%