Articles in this section

What formulas can I use in Insights+?

  • Updated

Summary

This article provides a comprehensive reference guide to formulas available in Insights+, including aggregate functions, statistical calculations, time-based analyses, and filtering criteria. Formulas enable users to create custom calculations and perform advanced data analysis without requiring technical expertise.

Prerequisites

  • Active Assure user account with access to Insights+ module
  • Permissions to create or edit dashboards and widgets
  • Basic understanding of Insights+ data sources and fields
  • Familiarity with dashboard creation in Insights+

Problem/Intent

Users need to perform complex data analysis and calculations within Insights+ to:

  • Create custom business logic and metrics
  • Combine data from multiple data sources
  • Apply conditional calculations based on specific criteria
  • Analyze trends, growth rates, and time-based comparisons
  • Summarize and aggregate data without manual preprocessing

Steps

1. Access Formula Builder in Insights+

  1. Log in to Assure
  2. Navigate to Insights+ from the main menu
  3. Open an existing dashboard or create a new dashboard
  4. Click Add Widget or edit an existing widget
  5. In the widget configuration panel, locate the Formulas section

2. Create a Basic Formula

  1. In the widget editor, click Add Formula or the formula icon
  2. Enter a name for the formula in the Formula Name field
  3. Select the data source from the Data Source dropdown
  4. Build the formula using field names, operators, and functions
  5. Click Validate to check formula syntax
  6. Click Apply to save the formula

3. Apply Aggregate Functions

  • Sum: Use Sum([Field Name]) to calculate the total of all values in a field
  • Average: Use Avg([Field Name]) to calculate the mean value
  • Count: Use Count([Field Name]) to count non-null records
  • Duplicate Count: Use DupCount([Field Name]) to count unique values
  • Max/Min: Use Max([Field Name]) or Min([Field Name]) to find the highest or lowest value

4. Use Statistical Functions

  • Median: Use Median([Field Name]) to find the middle value
  • Mode: Use Mode([Field Name]) to find the most frequently occurring value
  • Standard Deviation: Use Stdev([Field Name]) for sample standard deviation or Stdevp([Field Name]) for population standard deviation
  • Variance: Use Var([Field Name]) or Varp([Field Name])
  • Quartile: Use Quartile([Field Name], n) where n = 1, 2, or 3
  • Percentile: Use Percentile([Field Name], n) where n is the percentile value

5. Apply Conditional Filters to Formulas

  1. Select the field to filter within the formula builder
  2. Choose the filter type:
    • Value Filters: ≠, =, >, <, between
    • Text Filters: Contains, Doesn't Contain, Starts With, Ends With, Equals, Not Equal
    • List Filters: Include, Exclude
    • Ranking Filters: Top, Bottom
    • Time Filters: Date ranges, Calendar periods
  3. Enter the filter criteria values
  4. Combine multiple filters using AND/OR logic as needed

6. Create Time-Based Calculations

  • Past Periods:
    • PastYear([Field Name]) - Previous year value
    • PastQuarter([Field Name]) - Previous quarter value
    • PastMonth([Field Name]) - Previous month value
    • Prev([Field Name]) - Previous period value
    • Next([Field Name]) - Next period value
  • Cumulative Calculations:
    • YTDSum([Field Name]) - Year-to-date sum
    • QTDSum([Field Name]) - Quarter-to-date sum
    • MTDSum([Field Name]) - Month-to-date sum
    • YTDAvg([Field Name]) - Year-to-date average
    • QTDAvg([Field Name]) - Quarter-to-date average
    • MTDAvg([Field Name]) - Month-to-date average
  • Growth Analysis:
    • Growth([Field Name]) - Absolute growth value
    • GrowthRate([Field Name]) - Percentage growth rate
  • Time Differences:
    • YDiff([Date Field 1], [Date Field 2]) - Difference in years
    • QDiff([Date Field 1], [Date Field 2]) - Difference in quarters
    • MDiff([Date Field 1], [Date Field 2]) - Difference in months
    • DDiff([Date Field 1], [Date Field 2]) - Difference in days
    • HDiff([Date Field 1], [Date Field 2]) - Difference in hours
    • MnDiff([Date Field 1], [Date Field 2]) - Difference in minutes
    • SDiff([Date Field 1], [Date Field 2]) - Difference in seconds

7. Combine Multiple Fields and Data Sources

  1. Use mathematical operators: +, -, *, /
  2. Combine fields from the same data source: [Field A] + [Field B]
  3. To combine fields from different data sources, ensure data sources are properly linked in the widget configuration
  4. Example: Sum([Incidents.Total Cost]) / Count([Incidents.ID])

8. Test and Validate the Formula

  1. Click Validate in the formula builder to check for syntax errors
  2. Review the validation message for any issues
  3. Correct errors as needed (common issues: missing brackets, incorrect field names, unsupported functions)
  4. Apply the formula to the widget
  5. Preview the widget to verify calculation results
  6. Compare results with known values or manual calculations to ensure accuracy

Notes & Warnings

  • Formula field names must be enclosed in square brackets: [Field Name]
  • Formulas recalculate automatically when filters, variables, or granularity levels change
  • Not all functions are available for all data types (e.g., Sum cannot be applied to text fields)
  • Date fields must be in proper date format for time functions to work correctly
  • Division by zero will result in an error or null value
  • Performance may be impacted when using complex formulas on very large data sets
  • Widgets may fail to load if the formula includes more than 15 values.
  • Formula names must be unique within each widget
  • Some statistical functions may require minimum data set sizes to return meaningful results

Examples

Example 1: Calculate Average Incident Cost

Goal: Calculate the average cost per incident

Formula:

Avg([Incident.Total Cost])

Use Case: Display the average cost in a KPI widget to track incident financial impact

Example 2: Count High-Priority Open Actions

Goal: Count actions that are open and marked as high priority

Formula:

Count([Actions.ID]) WHERE [Actions.Status] = "Open" AND [Actions.Priority] = "High"

Use Case: Display a count widget showing urgent outstanding actions

Example 3: Calculate Year-over-Year Incident Growth Rate

Goal: Compare current year incidents to previous year and calculate percentage change

Formula:

GrowthRate(Count([Incident.ID]))

Use Case: Show incident trend in a line chart to visualize safety performance improvement or decline

Example 4: Calculate Rolling 90-Day Average of Incidents

Goal: Show smoothed trend of incidents over the past 90 days

Formula:

RollingAvg(Count([Incident.ID]), 90)

Use Case: Create a trend widget to monitor incident frequency with reduced daily volatility

Example 5: Calculate Time to Close Incidents (in Days)

Goal: Measure the average number of days from incident creation to closure

Formula:

Avg(DDiff([Incident.Date Created], [Incident.Date Closed]))

Use Case: Monitor incident response efficiency in a KPI or bar chart widget

Troubleshooting

Issue: "Invalid Field Name" Error

Cause: Field name is misspelled, does not exist in the selected data source, or is not enclosed in square brackets

Resolution:

  1. Verify the exact field name in the data source schema
  2. Ensure field name is enclosed in square brackets: [Field Name]
  3. Check that the correct data source is selected
  4. Confirm field visibility and permissions

Issue: Formula Returns Null or Blank Values

Cause: Missing or null data in source fields, division by zero, or mismatched data types

Resolution:

  1. Check the underlying data for null values
  2. Use Count() function to verify data availability: Count([Field Name])
  3. For division formulas, add a condition to handle zero denominators:
  4. IF([Denominator Field] = 0, 0, [Numerator Field] / [Denominator Field])
  5. Verify that all fields in the formula contain compatible data types

Issue: Time-Based Functions Not Working

Cause: Date field is not in proper date format, or time period field is incorrectly configured

Resolution:

  1. Verify date field format in the data source (must be recognized as Date type)
  2. Check that date values are complete and valid (not text or improperly formatted)
  3. Ensure calendar hierarchy is properly configured in Insights+ settings
  4. Use the Date and Calendar filter to confirm date field is functioning correctly

Issue: "Syntax Error" Message with No Details

Cause: Missing closing bracket, incorrect function syntax, or unsupported operator

Resolution:

  1. Check for balanced parentheses and brackets
  2. Review function syntax against this article's reference table
  3. Remove special characters or extra spaces
  4. Simplify complex formulas by breaking them into smaller parts for testing
  5. Verify operator precedence (multiplication and division before addition and subtraction)

Issue: Performance Degradation with Complex Formulas

Cause: Formula operates on large data sets, multiple nested functions, or numerous conditional filters

Resolution:

  1. Limit the time range using date filters
  2. Apply filters at the widget or dashboard level rather than within the formula
  3. Reduce the number of nested functions
  4. Consider pre-aggregating data in the source system if possible
  5. Contact Evotix Technical Support for optimization assistance

Related Articles

  • Creating Dashboards in Insights+: Step-by-step guide to building custom dashboards
  • Understanding Insights+ Data Sources: Overview of available data sources and field mappings
  • Insights+ Widget Configuration Guide: How to configure and customize widget display options
  • Filtering Data in Insights+: Applying filters to dashboards and widgets
  • Scheduled Reports in Insights+: Automating report generation and distribution

Suggested Related Articles to Look Up or Create

  1. Insights+ Formula Syntax and Best Practices: Detailed guide on formula structure, naming conventions, error handling, and optimization tips
  2. Working with Calculated Fields vs. Formulas in Insights+: Comparison of when to use calculated fields versus formulas, including performance considerations
  3. Creating Custom KPIs in Insights+: Step-by-step guide to building key performance indicator widgets using formulas
  4. Time Intelligence in Insights+: Advanced guide to time-based calculations, fiscal year setups, and period-over-period analysis
  5. Combining Multiple Data Sources in Insights+ Formulas: How to join and relate data from different modules for unified reporting
  6. Using Conditional Logic in Insights+ Formulas: Guide to IF statements, CASE logic, and nested conditions
  7. Troubleshooting Common Formula Errors in Insights+: Expanded troubleshooting guide with detailed error messages and resolutions
  8. Insights+ Performance Optimization for Large Data Sets: Best practices for formula performance, data caching, and query optimization

Cross-Module Functionality:

  • Formulas in Insights+ can reference data from any Assure module, including Incidents, Actions, Risk Assessments, Audits, Observations, Equipment Register, Person Register, Supplier Contractor Register, and custom modules
  • Insights+ dashboards can be shared across organizational units, allowing different teams to view the same calculated metrics with filtered data
  • Formula calculations are independent of Learn and Evotix 360 but can analyze training completion data from Learn if integrated with Assure

Dependencies:

  • Formulas rely on accurate data entry in underlying Assure modules
  • Date-based formulas require correct date field configuration
  • Cross-module formulas depend on proper data relationships and field mappings

AI Metadata

  • Product Area: Assure > Insights+
  • User Role: EHS Manager, Safety Officer, Data Analyst, System Administrator
  • Tags: Insights+, formulas, calculations, aggregate functions, statistical analysis, time-based analysis, data analysis, KPI, dashboards, widgets
  • Version/Region: Assure (all versions with Insights+ enabled)
  • Important Synonyms: calculated fields, custom calculations, formula builder, Insights formulas, business logic, metrics, data aggregation
  • Suggested Embedding Keywords: Insights+ formulas, Sum, Average, Count, Max, Min, Median, YTDSum, GrowthRate, PastYear, DDiff, conditional filters, aggregate functions, statistical functions, time functions, rolling average, year-to-date, formula syntax, calculated metrics, KPI formulas
Return to top

Related articles