This article is about our advanced editor
Formulas are at the core of Plecto. Every number that is displayed on a dashboard comes from a formula. With them, you can express a wide range of calculations that should be made on your data.
Formulas are saved as plain text, however, for it to work correctly it must follow a very specific syntax. You can think of it as a programming language. The language is similar to that of Excel or other spreadsheet software.
Your data in Excel are rows in a spreadsheet and you can filter and manipulate this data based on it's columns. Similarly in Plecto, registrations are the rows and fields are the columns.
We will use the following table's data as examples in the below functions:
Count(Data Source, <filters>)
The Count keyword tells Plecto to return the number of all registrations in the selected data source.
Count(Example data) = 3
Sum(Data Source, <filters>, Field)
The Sum keyword tells Plecto to return the sum of a given field of all registrations in the selected data source.
Sum(Example data, Amount Sold) = 45 000
First(Data Source, <filters>, Field)
First(Example data, Amount Sold) = 20 000
Last(Data Source, <filters>, Field)
Last(Example data, Amount Sold) = 15 000
Avg(Data Source, <filters>, Field)
Avg(Example data, Amount Sold) = 15 000
Max(Data Source, <filters>, Field)
Max(Example data, Amount Sold) = 20 000
Min(Data Source, <filters>, Field)
Min(Example data, Amount Sold) = 10 000
Very often we want to return the value of only some registrations. In our previous example we showed the total amount of all deals. A more useful formula would be the total amount of all won deals. This would effectively show us how much money we made. We can do this by using a filter in the Sum method like so:
The second argument
(Status="won") in the lookup method is a filter. It tells Plecto to only apply the sum on the items that match these criteria. It's of course possible to do the same with Count and other lookup methods.
Furthermore, multiple filters can be used at the same time by using a & between them. Let's say we only want won deals for our "Outbound" pipeline:
The two filters will now be applied to our data and only the deals that are both won and in the Outbound pipeline will be used for the sum.
The available comparators that we can use in our filters are:
= - Equal
!= - Not equal
> - Greater than
< - Less than
>= - Greater than or equal to
<= - Less than or equal to
So for example, if we wanted to display how many very big deals we won, we could do: