## An overview of lookup functions in the Advanced formula 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.

Our lookup functions are as follows

We will use the following table's data as examples in the below functions:

**Count**

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

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

First(Data Source, <filters>, Field)

The *First* keyword tells Plecto to return the first registration in the selected data source:

First(Example data, Amount Sold) = 20 000

**Last**

Last(Data Source, <filters>, Field)

The *Last* keyword tells Plecto to return the latest registration in the selected data source:

Last(Example data, Amount Sold) = 15 000

**Avg**

Avg(Data Source, <filters>, Field)

The *Avg* keyword tells Plecto to return the average of a given field of all registrations in the selected data source:

Avg(Example data, Amount Sold) = 15 000

**Max**

Max(Data Source, <filters>, Field)

The *Max* keyword tells Plecto to return the highest value of a given field of all registrations in the selected data source:

Max(Example data, Amount Sold) = 20 000

**Min**

Min(Data Source, <filters>, Field)

The *Min* keyword tells Plecto to return the lowest value of a given field of all registrations in the selected data source:

Min(Example data, Amount Sold) = 10 000

### Filters

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:

Sum(Deals,Status="won",Amount)

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:

Sum(Deals,Status="won"&Pipeline="Outbound",Amount)

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.

Comparators

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:

Count(Deals,Status="won"&Amount>=1000)