With a calculated field, you can create a new value from data that already exists in Insightly. This new field will be saved to your Values list and can be used in your charts. For example, your Average Order Value is calculated using the formula: SUM(“Sale”/“Orders”).
You'll use T-SQL (Transact-SQL) to build a calculated field. If you're not familiar with SQL or haven't created similar formulas in Excel, you might want to get assistance from someone who is more familiar with these things.
To create a calculated field:
- From the dashboard card edit page, click Add Calculated Field.
- Enter a name for the field. When you save the new field, this name will appear in the Categories or Values list above with an equals sign (=) in front of it.
- Enter a formula in the Formula field. You can build a formula by double-clicking any item in the Dataset or Functions lists, typing directly in the field, or copying and pasting a provided formula, such as the examples at the bottom of this article.
Select any function to learn about it. Each function includes a description, examples, and a link to more information about how to use it.
Click Save And Close.
Your new field will now appear in the Categories or Values list with an equals sign in front of it.
Here are some common calculated fields to get you started:
SUM(CASE WHEN "Is Open" = 0 THEN 1 ELSE 0 END)
SUM(CASE WHEN "Is Won" = 1 THEN 1 ELSE 0 END)
CASE WHEN SUM(CASE WHEN "Is Open" = 0 THEN 1 ELSE 0 END) = 0 THEN 0 ELSE
CAST(SUM("Is Won") AS FLOAT)/CAST(SUM(CASE WHEN "Is Open" = 0 THEN 1 ELSE 0 END) AS FLOAT)
Opportunities to Close
CAST(SUM(CASE WHEN “Is Open” = 0 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(“Opportunity Id”)
Change dates to Month, Year format
CASE WHEN "Date Created" IS NULL THEN NULL ELSE Cast(CONCAT(DATENAME("month", "Date Created"), ' ', DATENAME("year", "Date Created")) AS DATETIME) END