Skip to main content

Formula Fields

Stop guessing. Copy these formulas, customize the field names, and go.

How Formulas Work​

FORMULA ANATOMY
─────────────────────────────────────────────────────────────────

{fieldid}           →  Reference a field from your dataset
FUNCTION()          →  SQL function (CASE, SUM, ROUND, etc.)
'text'              →  Text literal (single quotes)
123                 →  Number literal

Example:
┌─────────────────────────────────────────────────────────────┐
│  CASE                                                       │
│    WHEN {daysoverdue} > 90 THEN 'Critical'                 │
│    WHEN {daysoverdue} > 30 THEN 'Warning'                  │
│    ELSE 'OK'                                                │
│  END                                                        │
└─────────────────────────────────────────────────────────────┘
         ↓
Output Type: STRING

Copy-Paste Formula Library​

AR Aging Bucket​

Use case: Categorize invoices by how overdue they are.

CASE
  WHEN CURRENT_DATE - {duedate} <= 0 THEN 'Current'
  WHEN CURRENT_DATE - {duedate} <= 30 THEN '1-30 Days'
  WHEN CURRENT_DATE - {duedate} <= 60 THEN '31-60 Days'
  WHEN CURRENT_DATE - {duedate} <= 90 THEN '61-90 Days'
  ELSE '90+ Days'
END

Output Type: STRING

Days Outstanding​

Use case: How many days since transaction date.

CURRENT_DATE - {trandate}

Output Type: INTEGER

Days Until Due​

Use case: Countdown to due date (negative = overdue).

{duedate} - CURRENT_DATE

Output Type: INTEGER

Gross Margin Percentage​

Use case: Profitability per transaction or line.

CASE
  WHEN {amount} = 0 THEN 0
  ELSE ROUND(({amount} - {costestimate}) / NULLIF({amount}, 0) * 100, 2)
END

Output Type: FLOAT

Gross Profit​

Use case: Revenue minus cost.

{amount} - {costestimate}

Output Type: FLOAT

Inventory Stock Status​

Use case: Categorize items by stock level.

CASE
  WHEN {quantityonhand} <= 0 THEN 'OUT OF STOCK'
  WHEN {quantityonhand} < {reorderpoint} THEN 'REORDER NOW'
  WHEN {quantityonhand} < {preferredstocklevel} THEN 'LOW STOCK'
  ELSE 'IN STOCK'
END

Output Type: STRING

Order Fulfillment Status​

Use case: Track order completion.

CASE
  WHEN {quantityshiprecv} = 0 THEN 'Not Shipped'
  WHEN {quantityshiprecv} < {quantity} THEN 'Partial'
  ELSE 'Complete'
END

Output Type: STRING

Customer Risk Score​

Use case: Flag high-risk customers based on payment behavior.

CASE
  WHEN {daysoverdue} > 90 THEN 'High Risk'
  WHEN {daysoverdue} > 60 THEN 'Medium Risk'
  WHEN {daysoverdue} > 30 THEN 'Watch'
  ELSE 'Good Standing'
END

Output Type: STRING

Amount This Year​

Use case: Only count amounts from current year.

CASE
  WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
  THEN {amount}
  ELSE 0
END

Output Type: FLOAT

Amount Last Year​

Use case: Only count amounts from previous year.

CASE
  WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
  THEN {amount}
  ELSE 0
END

Output Type: FLOAT

Amount This Quarter​

Use case: Filter to current quarter.

CASE
  WHEN TO_CHAR({trandate}, 'Q') = TO_CHAR(CURRENT_DATE, 'Q')
    AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
  THEN {amount}
  ELSE 0
END

Output Type: FLOAT

Amount This Month​

Use case: Filter to current month.

CASE
  WHEN EXTRACT(MONTH FROM {trandate}) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
  THEN {amount}
  ELSE 0
END

Output Type: FLOAT

YoY Growth Indicator​

Use case: Show if this year is up or down vs last year.

CASE
  WHEN {thisyearamount} > {lastyearamount} THEN 'Up'
  WHEN {thisyearamount} < {lastyearamount} THEN 'Down'
  ELSE 'Flat'
END

Output Type: STRING

Transaction Age Category​

Use case: Group transactions by age.

CASE
  WHEN CURRENT_DATE - {trandate} <= 7 THEN 'This Week'
  WHEN CURRENT_DATE - {trandate} <= 30 THEN 'This Month'
  WHEN CURRENT_DATE - {trandate} <= 90 THEN 'This Quarter'
  ELSE 'Older'
END

Output Type: STRING

Deal Size Category​

Use case: Segment deals by value.

CASE
  WHEN {amount} >= 100000 THEN 'Enterprise'
  WHEN {amount} >= 25000 THEN 'Mid-Market'
  WHEN {amount} >= 5000 THEN 'SMB'
  ELSE 'Small'
END

Output Type: STRING

Quarter from Date​

Use case: Extract quarter for grouping.

CONCAT('Q', TO_CHAR({trandate}, 'Q'))

Output Type: STRING

Year-Month Label​

Use case: Create readable date labels.

TO_CHAR({trandate}, 'YYYY-MM')

Output Type: STRING

Fiscal Quarter​

Use case: Map to fiscal calendar (adjust month offset as needed).

CASE
  WHEN EXTRACT(MONTH FROM {trandate}) IN (1, 2, 3) THEN 'FQ4'
  WHEN EXTRACT(MONTH FROM {trandate}) IN (4, 5, 6) THEN 'FQ1'
  WHEN EXTRACT(MONTH FROM {trandate}) IN (7, 8, 9) THEN 'FQ2'
  ELSE 'FQ3'
END

Output Type: STRING

First Day of Month​

Use case: Normalize dates for grouping.

TRUNC({trandate}, 'MONTH')

Output Type: DATE

Last Day of Month​

Use case: Month-end calculations.

LAST_DAY({trandate})

Output Type: DATE

Safe Division (Avoid Divide by Zero)​

Use case: Any calculation with division.

{numerator} / NULLIF({denominator}, 0)

Output Type: FLOAT

With default value if zero:

NVL({numerator} / NULLIF({denominator}, 0), 0)

Percentage of Total​

Use case: Line amount as percentage of transaction total.

ROUND({lineamount} / NULLIF({totalamount}, 0) * 100, 2)

Output Type: FLOAT

Currency Conversion​

Use case: Convert to base currency.

TO_NUMBER({foreignamount#converted})

Output Type: FLOAT

Multi-Currency Consolidation​

Use case: Consolidate across currencies.

TO_NUMBER({amount#currency_consolidated})

Output Type: FLOAT

Real-World Formula Scenarios​

Scenario 1: Sales Commission Calculation​

Business rule: 5% on deals under $50K, 7% on deals $50K+

CASE
  WHEN {amount} >= 50000 THEN ROUND({amount} * 0.07, 2)
  ELSE ROUND({amount} * 0.05, 2)
END

Output Type: FLOAT

Scenario 2: Lead Score​

Business rule: Score leads based on multiple factors.

CASE
  WHEN {leadsource} = 'Referral' AND {projectedamount} > 50000 THEN 'A'
  WHEN {leadsource} = 'Referral' OR {projectedamount} > 50000 THEN 'B'
  WHEN {projectedamount} > 10000 THEN 'C'
  ELSE 'D'
END

Output Type: STRING

Scenario 3: Payment Terms Status​

Business rule: Check if payment is within terms.

CASE
  WHEN {status} = 'Paid In Full' THEN 'Paid'
  WHEN CURRENT_DATE <= {duedate} THEN 'On Time'
  WHEN CURRENT_DATE - {duedate} <= 7 THEN 'Grace Period'
  ELSE 'Overdue'
END

Output Type: STRING

Scenario 4: Opportunity Win Probability​

Business rule: Estimate win likelihood by stage.

CASE
  WHEN {entitystatus} = 'Closed Won' THEN 100
  WHEN {entitystatus} = 'Negotiation' THEN 75
  WHEN {entitystatus} = 'Proposal' THEN 50
  WHEN {entitystatus} = 'Qualification' THEN 25
  ELSE 10
END

Output Type: INTEGER

Scenario 5: Weighted Pipeline Value​

Business rule: Amount × probability.

{projectedamount} * {probability} / 100

Output Type: FLOAT

Function Quick Reference​

Text Functions​

FunctionWhat It DoesExample
CONCAT(a, b)Join textCONCAT({firstname}, ' ', {lastname})
UPPER(text)UppercaseUPPER({companyname})
LOWER(text)LowercaseLOWER({email})
SUBSTR(text, start, len)Extract partSUBSTR({phone}, 1, 3)
LENGTH(text)Count charsLENGTH({description})
TRIM(text)Remove spacesTRIM({address})

Number Functions​

FunctionWhat It DoesExample
ROUND(num, decimals)RoundROUND({amount}, 2)
CEIL(num)Round upCEIL({quantity})
FLOOR(num)Round downFLOOR({quantity})
ABS(num)Absolute valueABS({variance})
NVL(val, default)Replace NULLNVL({discount}, 0)
NULLIF(a, b)NULL if equalNULLIF({qty}, 0)

Date Functions​

FunctionWhat It DoesExample
CURRENT_DATETodayCURRENT_DATE
EXTRACT(part FROM date)Get partEXTRACT(MONTH FROM {trandate})
ADD_MONTHS(date, n)Add monthsADD_MONTHS({trandate}, 3)
LAST_DAY(date)Month endLAST_DAY({trandate})
TRUNC(date, 'MONTH')Month startTRUNC({trandate}, 'MONTH')
TO_CHAR(date, format)Format dateTO_CHAR({trandate}, 'YYYY-MM')

Common Mistakes & Fixes​

Mistake 1: Division by Zero​

❌ Wrong:
{revenue} / {cost}

✅ Fixed:
{revenue} / NULLIF({cost}, 0)

Mistake 2: Wrong Output Type​

Problem: Formula returns text but you selected FLOAT

Fix: Match output type to what formula returns
- CASE returning 'text' → STRING
- Math calculations → FLOAT
- Date math → INTEGER or DATE

Mistake 3: NULL Handling​

❌ Wrong (NULL + 100 = NULL):
{discount} + 100

✅ Fixed:
NVL({discount}, 0) + 100

Mistake 4: Missing END in CASE​

❌ Wrong:
CASE WHEN {amount} > 0 THEN 'Positive'

✅ Fixed:
CASE WHEN {amount} > 0 THEN 'Positive' ELSE 'Zero/Negative' END

Creating a Formula: Step by Step​

FORMULA CREATION FLOW
─────────────────────────────────────────────────────────────────

1. Open Dataset Builder
2. Click "Formulas" above Records list
3. Click "New Formula"

┌─────────────────────────────────────────────────────────────┐
│  NEW FORMULA FIELD                                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Name: [ Aging_Bucket                    ]                  │
│                                                             │
│  Output Type: [ STRING ▼ ]                                  │
│                                                             │
│  Formula:                                                   │
│  ┌─────────────────────────────────────────────────────┐   │
│  │ CASE                                                 │   │
│  │   WHEN CURRENT_DATE - {duedate} <= 30 THEN '0-30'   │   │
│  │   WHEN CURRENT_DATE - {duedate} <= 60 THEN '31-60'  │   │
│  │   ELSE '60+'                                         │   │
│  │ END                                                  │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                             │
│  [Validate]  [Save]                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

4. Click Validate (catches errors before saving)
5. Click Save
6. Drag formula to Data Grid to use it

What's Next​

GoalGo To
Optimize workbook performanceBest Practices →
Build visualizationsTables, Pivots & Charts →