Formulas allow you to create calculated columns that automatically update when referenced data changes, ensuring your visualizations always reflect the latest calculations.
Formulas in Mappica enable you to create dynamic, calculated columns that derive their values from other columns in your dataset. When you enable a formula on a column, it becomes locked for manual editing and automatically recalculates whenever the referenced columns change. This ensures data consistency and eliminates the need for manual updates.
Formula columns can be used just like other dataset columns in components like charts, maps, tables, and filters. They support both numeric calculations and text manipulation.
Formulas work with all numeric field types, providing full mathematical and logical operations:
Text formulas support string manipulation and conditional logic:
Reference other columns using the # symbol followed by the field name. When you type #, a dropdown will appear showing available fields you can reference:
#Revenue + #Costs
#First Name + " " + #Last Name
+ Addition- Subtraction* Multiplication/ Division^ Exponentiation% ModuloFunction | Syntax | Description | Example |
|---|---|---|---|
| MIN | MIN(value1, value2, ...) | Returns smallest value | MIN(Federal, State, Local) |
| MAX | MAX(value1, value2, ...) | Returns largest value | MAX(Q1, Q2, Q3) |
| ABS | ABS(value) | Returns absolute value | ABS(Temperature) |
| ROUND | ROUND(value, decimal_places) | Rounds to specified decimals | ROUND(Average Score, 2) |
| ROUND | ROUND(value) | Rounds to nearest integer | ROUND(Test Score) |
| FLOOR | FLOOR(value) | Rounds down to integer | FLOOR(Test Score) |
| CEIL | CEIL(value) | Rounds up to integer | CEIL(Rating) |
| SQRT | SQRT(value) | Square root | SQRT(Population Density) |
| POW | POW(base, exponent) | Power function | POW(Growth Rate, 2) |
Use IF statements to create conditional calculations based on data values.
Syntax: IF(condition, value_if_true, value_if_false)
Supported Operators:
>, <, >=, <=, =, !=& (AND), | (OR)Examples:
Formula | Description |
|---|---|
| IF(#Unemployment Rate > 5, "High Risk", "Low Risk") | Categorizes risk based on unemployment rate |
| IF(#Revenue > 1000000, #Revenue * 0.1, #Revenue * 0.05) | Applies different tax rates based on revenue threshold |
| IF(#Status = "Active", "Yes", "No") | Converts status to Yes/No format |
| IF(#Age >= 18 & #Income > 50000, "Eligible", "Not Eligible") | Complex condition using AND operator |
Nested IF Support: Complex nested conditions with proper parentheses handling:
IF(#Score >= 90, "A",
IF(#Score >= 80, "B",
IF(#Score >= 70, "C", "F")))
Text functions are available for text field types and enable string manipulation and formatting.
Function | Syntax | Description | Example |
|---|---|---|---|
| CONCAT | CONCAT(text1, text2, ...) | Combines text values | CONCAT(First Name, " ", Last Name) |
| UPPER | UPPER(text) | Converts to uppercase | UPPER(City Name) |
| LOWER | LOWER(text) | Converts to lowercase | LOWER(Department) |
| LENGTH | LENGTH(text) | Returns character count | LENGTH(Description) |
Formula | Purpose |
|---|---|
| (#Revenue - #Costs) / #Revenue * 100 | Calculate profit margin percentage |
| IF(#Revenue > 50000, #Revenue * 0.25, #Revenue * 0.15) | Progressive tax calculation |
| ROUND((#Q1 + #Q2 + #Q3 + #Q4) / 4, 2) | Rounded quarterly average |
| #Principal * POW(1 + #Rate, #Years) | Compound interest calculation |
Formula | Purpose |
|---|---|
| CONCAT(#First Name, " ", #Last Name) | Combine first and last names |
| IF(#Active = 1, UPPER("Active"), LOWER("Inactive")) | Status formatting with case |
| LENGTH(#Description) | Count characters in description |
| CONCAT(UPPER(#City), ", ", #State) | Formatted location string |
Formula | Purpose |
|---|---|
| MAX(#Values) - MIN(#Values) | Calculate data range |
| SQRT(#Variance) | Standard deviation from variance |
| POW(#Final Value / #Initial Value, 1 / #Years) - 1 | Compound annual growth rate |
| (#Value - #Mean) / #Standard Deviation | Z-score calculation |
# to trigger field suggestionsPro Tip: Use the function help system by hovering over functions in the formula editor to see syntax, descriptions, and examples.
Formula columns automatically update when: