7 min read
Understanding Calculated Fields
Create powerful calculated fields for custom analysis
What are Calculated Fields?
Calculated fields are custom formulas you create for new data.
Example:
- Profit Ratio = Profit / Sales
- Full Name = First Name + " " + Last Name
Creating Calculated Fields
- Right-click in Data Pane
- Select "Create Calculated Field"
- Name your field
- Write formula
- Click OK
Basic Math
// Addition
[Sales] + [Shipping Cost]
// Subtraction
[Sales] - [Cost]
// Multiplication
[Price] * [Quantity]
// Division
[Profit] / [Sales]
Common Functions
Text Functions
// Combine text
[First Name] + " " + [Last Name]
// Uppercase
UPPER([Name])
// Get first 3 characters
LEFT([Code], 3)
Number Functions
// Round to 2 decimals
ROUND([Value], 2)
// Absolute value
ABS([Profit])
// Minimum of two values
MIN([Budget], [Actual])
Date Functions
// Get year
YEAR([Date])
// Get month
MONTH([Date])
// Days between dates
DATEDIFF('day', [Start], [End])
IF Statements
IF [Profit] > 0 THEN "Profit"
ELSEIF [Profit] < 0 THEN "Loss"
ELSE "Break Even"
END
Common Calculated Fields
Profit Ratio
[Profit] / [Sales]
Year-over-Year Growth
([This Year Sales] - [Last Year Sales]) / [Last Year Sales]
Running Total
RUNNING_SUM(SUM([Sales]))
Discount Category
IF [Discount] = 0 THEN "No Discount"
ELSEIF [Discount] < 0.2 THEN "Low Discount"
ELSE "High Discount"
END
Tips
- Use clear names
- Test with simple data first
- Check for nulls (use IFNULL)
- Comment complex formulas
Validation
Green checkmark ✓ = Formula is valid Red X = Error in formula
Common errors:
- Missing field names
- Wrong function syntax
- Mismatched data types
Quick Reference
| Type | Example |
|---|---|
| Math | [A] + [B] |
| Text | [A] + " " + [B] |
| Logic | IF [A] > 0 THEN "Yes" ELSE "No" END |
| Date | YEAR([Date]) |
| Null check | IFNULL([A], 0) |
Summary
Calculated fields create new data using formulas. Use math, text functions, and IF statements. Test and validate before using!