Knowing which calculation method to use in Cloverleaf is essential to receiving the right result. This can make the difference between data being meaningful or useless to your organization. Two main calculation functions Cloverleaf has are Aggregations and Row-Level calculations.
In this blog post, we will go over each calculation method and learn when and how they should be used. This will help to make even new users of the platform have a better understanding of how to control their data for best results.
Aggregates are column-based calculations. With this method, individual values are first aggregated, and the calculation will be performed against the result.
Aggregation options can vary depending on the type of raw data in the provided columns – numeric or non-numeric data. For numeric columns, aggregation options include (but not limited to): sum, count, variance, minimum, maximum, median, distinct count, average and etc. For non-numeric columns, aggregation options include: minimum, maximum, count and distinct count only.
Aggregates can be created in Formulate or in Discover.
For example, below we will create percentage variance calculation of sales and expenses in formulate.
Columns are aggregated individually first:
Then the calculation ([SUM(sales)] – [SUM(expenses)]) / ([SUM(sales)]) is performed.
This formula, can also be done directly in discovery for the same result. Just select your measures and right-click to create a new measure.
As in the name, row-level calculation are calculations that are first done at the row level and then aggregated. This results with the calculations being done for each row in your data.
Row-level calculations can be done in the Model module using “Calculated Columns,” Formulate or Discover using “Custom Columns” (a new function in the Pyramid 2023 release).
In Model, for this row-level calculation the SUM([Direct Earned Premium] + [Assumed Earned Premium] – Ceded Earned Premium]) is calculated by each row then aggregated.
This calculation can also be done in Formulate as shown below.
First step is to create the calculation and save as a new Formulation. In this case, I will save as “Schedule P.”
You will then select “Sum” under the “Aggregation Blocks”
Then you will then insert your created calculation by selection “Pick a Data Point” then “Measures”. Open the “FX” folder to locate your content and select “My Content” then your created function.
Knowing which calculations method to use is essential to achieving the right result with you data and for your company.
If there are any further questions, please don’t hesitate to reach out!