Calculated fields
A calculated field computes a value from the other fields: a margin, a weighted amount, a year, a delay in days, a category. It is written in a small expression language that the server checks and translates for the database — never free SQL. It belongs to the analysis and is saved with it.
At a glance
The editor
New calculated field, at the bottom of the fields pane, opens the editor; the pencil of a calculated field edits it, the bin deletes it (it also leaves the shelves).
| Field | Use |
|---|---|
| Name | A letter, then letters, digits or _; unique in the analysis. |
| Label | The name shown in the pane and the results. |
| Insert a field, Insert a function | Insert at the cursor; a function is inserted with its arguments selected. |
| Expression | The formula. |
| Test | Compiles the expression on the server and shows its type and eight sample values. |
Save is enabled once the test passes. The result type decides the field's role: a number becomes a measure, anything else a dimension. The field then appears in a calc group of the pane, and can be used in the rows, the measures and the filters like any other.
The language
A field is written by its name for the analysed entity (AMOUNT), or entity.NAME for another one (customers.COUNTRY). Text values go between single quotes.
| Element | Forms |
|---|---|
| Arithmetic | + - * / and brackets. A division by zero gives an empty value. |
| Comparison | = != <> < <= > >= |
| Logic | and, or, not |
| Condition | condition ? value_if_true : value_if_false |
| List | x in ('FR', 'BE') |
| Empty | x is empty, x is not empty |
| Dates | year(d), month(d), day(d), days(from, to) — the days between two dates |
| Numbers | round(x, n), abs(x) |
| Text | upper(s), lower(s), length(s), concat(a, b, …) |
| Any | coalesce(a, b, …) — the first non-empty value |
A yes/no result shows as Y / N. A JD Edwards EnterpriseOne Julian date is converted to a real date before a date function applies.
Examples
| Need | Expression | Role |
|---|---|---|
| Weighted pipeline | STAGE = 'Negotiation' ? AMOUNT * 0.6 : AMOUNT * 0.3 | measure |
| Unit price | AMOUNT / QTY | measure |
| Closing year | year(CLOSE_DATE) | measure — a number; to group by it, prefer the Year grouping of the date on Rows |
| Days to close | days(CREATED_ON, CLOSE_DATE) | measure |
| Size band | AMOUNT >= 100000 ? 'large' : 'standard' | dimension |
| Domestic or not | customers.COUNTRY = 'FR' | dimension (Y / N) |
Things to know
| Situation | What happens |
|---|---|
A type mismatch (AMOUNT + 'x'). | The test fails and gives the position of the error. |
| An unknown field. | The test fails and names it. |
The two branches of ? : have different types. | The test fails: both must be numbers, or both text, or both dates. |
| An expression over 2,000 characters. | Refused. |
What's next
- Analyse data — the shelves and views.
- Saved analyses and dashboards — a saved analysis keeps its calculated fields.