How to create a calculated field for an Insightly dashboard card

This article is part of the Guide to Insightly Dashboards

This article is for creating calculated fields to use in dashboard cards and uses T-SQL (Transact-SQL).
If you want to learn how to create a custom calculated field that will appear in your records, read How to create a custom calculated field instead.

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.

Sample Calculations

Here are some common calculated fields to get you started:

Opportunities Won
SUM(CASE WHEN "Current State" = 'WON' THEN 1 ELSE 0 END)

Total OpportunitiesCOUNT("Record ID")

YoY Calculated Field

FORMAT(CONVERT(datetime, "Lead Created"), 'yyyy')

Win Rate (Opportunities Won / Total Opportunities)
CAST(SUM(CASE WHEN "Current State" = 'WON' THEN 1 ELSE 0 END) AS DECIMAL(10,2)) / CAST(COUNT("Record ID") AS DECIMAL(10,2))

Methodology:
When a division is performed, the numerator and denominator need to be converted to decimals before performing the division. Either a CONVERT or CAST function can be used to convert the operands to decimals.
EXAMPLE: CAST(COUNT("Record ID") AS DECIMAL(10,2))

Change dates to Month, Year format
FORMAT(CONVERT(datetime, "Lead Created"), 'yyyy/MM')
Change date field "Lead Created" to relevant date field

Change dates to Month, Day, Year format
FORMAT(CONVERT(DATETIME, "Opportunity Created"), 'MM/dd/yyyy')
Change date field "Opportunity Created" to relevant date field

Was this article helpful?