Auction Excellence - Parking Lot Management & Quality Inspection

Overview

Calculated fields allow you to create new columns derived from existing data using SQL formulas. These virtual columns appear alongside regular columns in the Query Builder, enabling powerful analytics without modifying your source data.

SQL Formulas

Write expressions using standard SQL syntax

Pre-built Templates

Start with common calculations for automotive data

Live Validation

Test formulas against real data before saving

Query Integration

Calculated fields work seamlessly in Query Builder

What Are Calculated Fields?

Calculated fields are virtual columns that compute values on-the-fly using SQL expressions. Unlike raw columns that store static data, calculated fields derive values from other columns.

Common Use Cases

Use Case	Example Formula	Result
Date Differences	`stop_date - start_date`	Days between two dates
Time Calculations	`EXTRACT(EPOCH FROM (end_time - start_time)) / 60`	Minutes duration
Conditional Values	`CASE WHEN sale_type = 'Lease' THEN 'Off-Lease' ELSE 'Consignment' END`	Categorization
Text Operations	`UPPER(LEFT(vin, 10))`	Formatted identifiers
Aggregation Ready	`price * quantity`	Computed values for sums

Creating Calculated Fields

Accessing the Editor

Navigate to Dataset

Go to Analytics → Datasets and select your dataset

Open Calculated Fields

Click the Calculated Fields tab or Edit Calculated Fields link

Add New Field

Click the Add Calculated Field button

Field Configuration

Field	Required	Description
Field Name	Yes	Technical name (no spaces, lowercase)
Display Name	Yes	Human-readable name shown in UI
Description	No	Explanation for AI context
Formula	Yes	SQL expression
Source Columns	Auto	Columns used in formula (auto-detected)
Result Type	Yes	Expected output type (number, string, date)
Usage Examples	No	Example queries using this field

Formula Editor

The formula editor provides:

Syntax highlighting for SQL keywords
Column autocomplete - start typing to see matching columns
Validation - check formula syntax before saving
Live preview - see sample results from actual data

Formulas are validated against your dataset’s actual schema and data. Invalid column references or syntax errors are caught before saving.

Formula Syntax

Calculated fields use PostgreSQL SQL syntax. Any valid SQL expression that can appear in a SELECT clause is supported.

Basic Arithmetic

-- Addition
column_a + column_b

-- Subtraction
end_date - start_date

-- Multiplication
price * 1.08  -- Add tax

-- Division (with null handling)
COALESCE(revenue / NULLIF(count, 0), 0)

Date Operations

-- Days between dates
completion_date - check_in_date

-- Extract parts
EXTRACT(YEAR FROM sale_date)
EXTRACT(MONTH FROM created_at)
EXTRACT(DOW FROM check_in_date)  -- Day of week (0-6)

-- Date arithmetic
check_in_date + INTERVAL '30 days'

-- Current date comparison
CURRENT_DATE - check_in_date AS days_on_lot

Time Calculations

-- Minutes between timestamps
EXTRACT(EPOCH FROM (end_time - start_time)) / 60

-- Hours between timestamps
EXTRACT(EPOCH FROM (completion_ts - start_ts)) / 3600

Conditional Logic

-- Simple CASE
CASE
  WHEN sale_type = 'Lease' THEN 'Off-Lease'
  WHEN sale_type = 'Consignment' THEN 'Dealer Consignment'
  ELSE 'Other'
END

-- CASE with ranges
CASE
  WHEN days_on_lot < 7 THEN 'Fresh'
  WHEN days_on_lot < 30 THEN 'Standard'
  ELSE 'Aged'
END

-- Null handling
COALESCE(preferred_value, fallback_value, 'default')

String Operations

-- Concatenation
first_name || ' ' || last_name

-- Substring
LEFT(vin, 10)
SUBSTRING(stock_number, 1, 5)

-- Case conversion
UPPER(sale_type)
LOWER(seller_name)
INITCAP(customer_name)

-- Pattern matching result
CASE WHEN vin LIKE '1%' THEN 'US' ELSE 'Import' END

Predefined Templates

The system includes templates for common automotive calculations. Click Use Template to populate a pre-built formula.

Available Templates

days_since_checkin

Formula: CURRENT_DATE - check_in_dateResult Type: numberDescription: Calculates the number of days since the vehicle was checked in. Useful for tracking lot aging and processing delays.Example Query: “Show average days since check-in by sale type”

days_to_cr

Formula: cr_completion_date - check_in_dateResult Type: numberDescription: Days from vehicle check-in to condition report completion. Key metric for CR turnaround time.Example Query: “What’s the average time to CR this month vs last month?”

cr_time_minutes

Formula: EXTRACT(EPOCH FROM (stop_time - start_time)) / 60Result Type: numberDescription: Duration of condition report in minutes. Used for writer productivity analysis.Example Query: “Show average CR time by writer”

days_to_sale

Formula: sale_date - check_in_dateResult Type: numberDescription: Total days from check-in to sale. Important for inventory turn analysis.Example Query: “What’s our average days-to-sale by sale type?”

age_at_sale

Formula: EXTRACT(YEAR FROM sale_date) - model_yearResult Type: numberDescription: Vehicle age in years at time of sale. Useful for fleet analysis.Example Query: “Show distribution of vehicle age at sale”

Using Templates

Click Use Template

In the Calculated Fields editor, click Use Template

Select Template

Choose from the available templates

Customize

Adjust column names to match your dataset’s schema

Validate & Save

Test the formula and save the field

Formula Validation

Real-Time Validation

As you type, the editor validates:

Syntax: SQL expression is well-formed
Column References: All referenced columns exist in the dataset
Type Compatibility: Operations are valid for the data types involved

Sample Execution

Click Test Formula to:

Execute the formula against sample data rows
See actual computed values
Verify the result type is correct

Test with sample data before saving. Some formulas may be syntactically valid but produce unexpected results (e.g., division by zero, null propagation).

Common Validation Errors

Error	Cause	Solution
”Column not found”	Typo in column name	Use autocomplete or check spelling
”Type mismatch”	Operation on wrong type	Cast values: `::INTEGER`, `::DATE`
”Syntax error”	Invalid SQL	Check parentheses, quotes, operators
”Division by zero”	Denominator can be zero	Use `NULLIF(column, 0)` wrapper

Using Calculated Fields

In Query Builder

Calculated fields appear in the Column Reference Panel under a “Calculated” category. They can be:

Selected for output columns
Used in WHERE conditions
Aggregated (SUM, AVG, COUNT)
Grouped in GROUP BY

Example Queries

Average Time-to-CR

With a days_to_cr calculated field:Query: “What’s the average days to CR completion by sale type?”Generated SQL:

SELECT sale_type, AVG(days_to_cr)
FROM dataset
GROUP BY sale_type

Aged Inventory Count

With a days_since_checkin calculated field:Query: “How many vehicles have been on lot more than 30 days?”Generated SQL:

SELECT COUNT(*)
FROM dataset
WHERE days_since_checkin > 30

Writer Productivity

With a cr_time_minutes calculated field:Query: “Show average CR time per writer for the last week”Generated SQL:

SELECT writer_name, AVG(cr_time_minutes)
FROM dataset
WHERE cr_date >= CURRENT_DATE - 7
GROUP BY writer_name

Best Practices

Formula Design

Do

Handle NULL values explicitly
Use COALESCE for defaults
Test edge cases
Document complex formulas

Don't

Assume data is always clean
Create overly complex formulas
Forget about division by zero
Skip validation testing

Performance Considerations

Calculated fields are computed at query time. For optimal performance:

Keep formulas simple when possible
Avoid nested subqueries in formulas
Use appropriate indexes on source columns
Consider materialized columns for frequently-used expensive calculations

Usage Examples Field

When adding a calculated field, include usage examples in the description. This helps the AI understand when to use the field:

Usage examples:
- "Show average days_to_cr by month"
- "How many vehicles took more than 5 days to CR?"
- "Compare days_to_cr between lease and consignment"

Troubleshooting

Formula returns NULL for all rows

Cause: NULL propagation from source columnsSolution: Wrap nullable columns with COALESCE:

COALESCE(end_date, CURRENT_DATE) - start_date

Division returns 0 instead of NULL

Cause: Integer division behaviorSolution: Cast to decimal and handle zero:

COALESCE(numerator::DECIMAL / NULLIF(denominator, 0), 0)

Date calculation returns unexpected values

Cause: Timestamps vs dates mixingSolution: Cast to appropriate type:

end_date::DATE - start_date::DATE

Field not appearing in Query Builder

Cause: Formula validation failed or field not savedSolution:

Check that validation passed (green checkmark)
Click Save explicitly
Refresh the Query Builder page

Column Metadata

Configure metadata for source columns

Metadata Templates

Apply pre-configured calculated fields from templates

Query Builder

Use calculated fields in natural language queries

Database Schema

Technical reference for dataset tables

Getting Started

User & Location Management

Operations

Analytics Module

Account

Data Management

Configuration

​Overview

SQL Formulas

Pre-built Templates

Live Validation

Query Integration

​What Are Calculated Fields?

​Common Use Cases

​Creating Calculated Fields

​Accessing the Editor

​Field Configuration

​Formula Editor

​Formula Syntax

​Basic Arithmetic

​Date Operations

​Time Calculations

​Conditional Logic

​String Operations

​Predefined Templates

​Available Templates

​Using Templates

​Formula Validation

​Real-Time Validation

​Sample Execution

​Common Validation Errors

​Using Calculated Fields

​In Query Builder

​Example Queries

​Best Practices

​Formula Design

Do

Don't

​Performance Considerations

​Usage Examples Field

​Troubleshooting

​Related Documentation

Column Metadata

Metadata Templates

Query Builder

Database Schema

Overview

What Are Calculated Fields?

Common Use Cases

Creating Calculated Fields

Accessing the Editor

Field Configuration

Formula Editor

Formula Syntax

Basic Arithmetic

Date Operations

Time Calculations

Conditional Logic

String Operations

Predefined Templates

Available Templates

Using Templates

Formula Validation

Real-Time Validation

Sample Execution

Common Validation Errors

Using Calculated Fields

In Query Builder

Example Queries

Best Practices

Formula Design

Performance Considerations

Usage Examples Field

Troubleshooting

Related Documentation