Data Functions

Data functions

The data functions, also known as lookup functions, are an integral part of formulas in Plecto. They tell Plecto to make calculations based on registrations and fields in your data sources.

If you are familiar with Excel, you can think of our data functions as the SUM, COUNT, AVG, and others used on Excel spreadsheets. You can find the data functions when building formulas in the formula editor. Learn more about the formula editor.

When making calculations with the data functions, please remember that the formula output depends on the default date field in your data source and the time period set on your widgets.

Example

Let's see how we can use the data functions in formulas based on the data source "Deals" given below. To better illustrate how the data functions work, we have set the time interval to all time in the widget preview in the formula editor.

The Sum of function tells Plecto to return the sum of registration values within a certain field in a data source. For example, to sum the values from registrations with the status "won," you can use a formula similar to the one below. The formula returns a value of 1320, as it selects the values from registrations with the status "won."

The Number of function tells Plecto to return the number of registrations in a data source. The returned number is relative to the default date field in the data source and the time period you set on the widget.

In our example, the data source "Deals" contains 6 registrations, and we have set the time interval to all time in the widget preview, which means that the formula will return 6. See the video below.

Use Number of unique to return the number of unique registration values from a field in your data source. With the example from our data source "Deals," the formula below will return 3, as there are only three unique values in the field "Status," namely "in progress," "canceled," and "won."

The First function tells Plecto to return the earliest (oldest) occurrence of a field value from a data source. For example, to see the first registration with the status "won" in the data source "Deals," you could create a formula like here below.

The default date field in the data source "Deals" is "Created date." Therefore, the formula will search for the earliest registration based on the "Created date" and output a value of 20, as this is the first registration with the status "won" in the data source.

The Last function tells Plecto to return the latest occurrence of a field value from a data source. For example, to see the last registration with the status "won" in the data source "Deals," you could create a formula like here below, and in our case, it will output a value of 1000.

The Minimum function tells Plecto to return the lowest value of a field from all registrations in a data source. To see the registration with the lowest "won" value, you could create a formula similar to the one below. In our example, the formula will output a value of 20 since it is the lowest value of all the registrations with the status "won."

The Maximum function tells Plecto to return the highest value of a field from all registrations in a data source. To see the biggest won deal in the data source "Deals," you could create a formula similar to the one below. The formula will output a value of 1000 since it is the highest value of all the registrations with the status "won."

Use the Average function to calculate the average registration value in a data source. For example, to calculate the average value of deals with the status "won" in the data source "Deals," you could create a formula similar to the one below. The formula will display a value of 440 since (1000 + 300 + 20)/3 = 440.

Add filters to calculate specific values from your data source. For example, you can add a filter to return the number of registrations with the status "won." The formula below will return 3, as there are three registrations in "Deals" that match the status "won."