Aggregates help you aggregate data in your solution based on relations. Let us take an example where we want to see the number of customer visits and the date of the last customer visit where you Browse your Customers, for instance in the Table view. In order to setup an Aggregate, you need to have the role of Administrator. Anyone can however use them once created. This is how you do it:
Create the filter set. In our example, we will create a filter set for History saying:
- Date = Previous 365 days (include today)
- Type = Customer visit
Save the filter. In our example, we are going to call it Customer visits last 365 days
First, you need a filter set configured.
Go to Lime Admin to add your Aggregate in the appropriate table. In our example, that would be Lime Admin ➡ Views ➡ Company ➡ Table and click + Columns
Create your Aggregate by picking
- Field name: the lime type which you want to aggregate. In our example, that would be History.
- The field which you want to Aggregate. In our example, we leave this empty since we only want to count the number of records.
- Column title. In our example, we are going to call this Nbr. of customer visits last 365 days.
- Calculate: which operator you want to use. In our example, that would be COUNT.
- Filter set: which filter set you want to use the operator for. In our example, that would be Customer visits last 365 days.
Example configuration of an Aggregate showing Number of customer visits last 365 days in the Company table.
In our example, we also want to add Date of last customer visit, which is done like this:
Example configuration of an Aggregate showing Date of last customer visit in the Company table.
Now we can use our Aggregate in the table, for example for:
- Sorting. For example, sort on Last customer visit to get the list sorted by those who need a visit the most
- Filtering. Filter out all that have less than 2 customer visits and create a To-do to contact them.
End result. Now you can sort and filter on your Aggregates.
The different operators one can use are:
- Count. Great for when you want to see how many there are of something. How many products, tickets, important contacts, etc.
- Sum. Great for fields that hold numbers, such as value. Sum the outstanding pipeline value or total time spent on tickets per customer.
- Minimum and Maximum. Great for only showing the smallest or biggest of something. The last date of a To-do or History note or the first point of contact. (Maximum date = Latest date. Minimum date = Earliest date.)
- Average. For example how long a tickets takes on average per customer.