# Create a calculated field > Define a column with a formula, browse and search every function in the reference panel, let Golden type it from what the formula returns or set a text formula to a date yourself, build dates with MAKEDATE and DATE, look up a value by date range, check it as you type, and format and save it. Prepare · 6 min read · Updated 2026-10-31 A calculated field is a column you define with a formula. Once saved, it behaves like any other field - you can drag it onto a chart, group or filter by it, or reference it from other calculations. ## Open the dialog 1. Open the Prepare view of your dataset. 2. Click the + button in the toolbar (Add field). 3. Choose Add new field - a dialog opens titled Create calculated field. In Analyze, you can also open the Fields tab of the Analyst pane and click New calculated field at the bottom of the Calculated section. To change an existing field, right-click it in a Fields list and choose Edit Formula, or choose Edit Calculation... from its column menu in Prepare. On the Discover, dashboard, Analyze or Prepare views you can also open the dialog from the command palette - press Cmd+K (Ctrl+K on Windows) and search for "calculated field". ## Build the formula Type a name and a formula. Click a field in the reference panel to insert it at the cursor as [Field Name], or type [ for suggestions - the list offers your fields, your other calculated fields, and functions as you type. Typing @ opens the same picker filtered to fields and parameters only, no functions, the way an @ mention works in chat; pick one and it is inserted as a [Field] reference. An @ never fires mid-word or inside quotes, so an email like a@b.com stays literal. The formula editor offers these building blocks: - Operators: + − × ÷ ( ) > < = , - Aggregates: SUM, AVG, MIN, MAX, COUNT, COUNTD, and FOREACH (an aggregate computed per group) - Math: ABS, ROUND, SAFE_DIVIDE (blank instead of an error when dividing by zero), and DISTANCE (great-circle distance from each row to a point) - Logic: IF(condition, trueValue, falseValue), CASE(field, val1, result1, ...), and [Field] IN (value1, value2, …) / [Field] NOT IN (…) to test whether a field matches any value in a list - the values must be literals, and IN can't follow arithmetic or a comparison - Text: CONCAT, LEFT, RIGHT, LEN, UPPER, LOWER - Date: YEAR, MONTH, DAY, DATEPART, DATEDIFF, DURATION, BUSINESSDAYS, TODAY, NOW, and MAKEDATE / DATE to build a date from parts or read one out of text The reference panel on the right of the dialog is a search box over three tabs - Functions, Fields, and Templates. Search by name to find a function or field across them. The Functions tab lists every function your connection supports, each with a short description, and a panel at the foot shows the signature, description, and an example for the function you point at - or for the call your cursor is inside, with the current argument highlighted. Click a function or field and it drops in at the cursor. ## Durations and business days - DURATION(start, end, unit, absolute) - the time between two dates. The unit is optional and defaults to days; it can be seconds, minutes, hours, days, weeks, months, or years. The result is negative when end is before start, unless you pass true as the fourth argument. - BUSINESSDAYS(start, end, absolute) - the number of weekdays, Monday through Friday, between two dates. Holidays are not excluded, and the end date isn't counted, so Monday to Wednesday is 2. Pass true as the third argument to always get a positive number. For example, DURATION([Order Date], [Ship Date], "hours") gives hours to ship, and BUSINESSDAYS([Opened], [Closed]) gives working days to resolve. ## Build a date from parts or from text When your dates arrived as separate columns or as text, MAKEDATE and DATE turn them into a real date you can group, filter, and put on a date axis. - MAKEDATE(year, month, day) - builds a date from three parts, each a field or a number, for example MAKEDATE([Year], 1, 1) for the first of each year. DATE(year, month, day) does the same. - DATE(text) - reads a date out of text such as "2019-01-01", and reads a bare four-digit year as January 1 of that year. - DATE(text, "MM/DD/YYYY") - names the layout when the text is in another order. Supported layouts include MM/DD/YYYY, DD/MM/YYYY, MMM DD, YYYY, and YYYY-MM-DD; an unrecognized layout is rejected. > **Note:** A part that is empty, isn't a number, or falls out of range - month 13, or February 30 - comes back empty rather than raising an error. ## Tag rows by a date range On an uploaded dataset with more than one table, RANGE_LOOKUP reads a value from another table whose start-to-end range contains each row's date - handy for tagging every record with the period, campaign, or season it falls in. Write it as `RANGE_LOOKUP([Order Date], [start.periods], [end.periods], [label.periods])`: the date to place, then the other table's start and end columns and the column to return, each named `[column.table]` the way the field list writes a column from another table. The first range that contains the date wins, ranked by its start, and both ends are included; a date that falls in no range comes back empty. It appears in the formula editor's function list for uploaded files only. > **Tip:** You don't have to write it by hand. When Golden spots another table with a start and end date pair beside your data, it offers a Tag by date range shortcut - pick the date column and click it - that builds the same field for you. ## Handle empty values - [Field] IS NULL and [Field] IS NOT NULL test whether a value is missing, for example IF([Discount] IS NULL, 0, [Discount]). - NULL on its own stands for an empty value, so IF([Status] = "Void", NULL, [Amount]) blanks out void rows. - COALESCE([Mobile], [Phone], "None") returns the first of its arguments that isn't empty. - SAFE_DIVIDE([Profit], [Revenue]) returns a blank instead of an error when the denominator is 0. ## Catch mistakes as you type Golden checks what type each part of the formula is while you type and explains a mismatch before you save. The message waits for a pause in your typing and shows as a calm callout - a headline and a hint - turning red only once you try to save. When the fix is mechanical, a button under the message offers it; click Use followed by the corrected formula to apply it. - Joining text with + - "+" adds numbers, so Golden suggests CONCAT instead. - Summing or averaging a text field - Golden suggests COUNTD to count distinct values. - Subtracting one date from another - Golden suggests DATEDIFF("day", …) to get the number of days between them. - Arithmetic on text, a number function on text, or comparing text with a number with < or > - Golden explains the problem. An unknown function or field name also shows a validation error before you save. ## See what the formula computes Once the formula is valid, the Explain calc link beside the formula box shows what it does on real data. It lays the formula out as a visual equation, replacing each [Field] with a value from a sample row and showing the result, and you can step through sample rows to check edge cases. A formula that aggregates reads "all rows" in its field slots rather than a single row's value, because the aggregate spans the rows. Explain calc isn't offered for a formula that reads more than one table. ## Use AI or a template The Generate with AI section accepts a plain-English description (for example, "profit margin as a % of revenue") and writes a formula for you. The Templates tab in the reference panel offers ready-made patterns such as Year-over-Year Growth %, Percentage Change, Days Between Dates, Business Days, Conditional Value, and Value Bucket, along with suggestions Golden tailors to your data. ## Formulas that aggregate A formula can summarize across rows. `IF(SUM([Sales]) > 200000, "High", "Other")` compares a group's total to a threshold, and a field like this produces one value per group rather than one per row — so it reads correctly in a table cell, a label, a tooltip, or on the Color shelf, where the chart has already grouped the data. To put an aggregate field on an axis — where it becomes the grouping itself — name the grain with FOREACH. `FOREACH([Customer], SUM([Sales]))` computes the sum per customer on every row, which is what lets you group a chart by it. ## Create a field from the chat You don't need the dialog at all. In the Analyst pane on a sheet, the Storyteller pane on a dashboard, or the Data Agent pane in Prepare, ask for the field in plain words - "add a field for profit margin" - and the chat creates it. It can also change or delete a calculated field when you ask. ## Format and save The Display format section shows the Field type - Number, Date, or Text. For most formulas the type follows what the formula returns, but when a formula returns text you can set the Field type to Date yourself: Golden wraps the formula in DATE() and shows a Read text as picker, where you choose Automatic or a named date layout. Set it back to Text to unwrap it. The other choice is Geographic, which you pick when the field holds a place you want to map. For a Number field, pick a Format - Standard, Currency (with USD and other codes), Percentage, Duration with a unit and display style, or Scientific - and a Date field offers date formats instead. A Duration shown in its human or compact style also offers a Smallest unit (Any, or Minutes through Years) that sets the smallest unit it shows. A formula that returns plain text has no number format to set. Click Create field to add it to your dataset, or Update field when editing. Cmd+↵ (Ctrl+↵ on Windows) saves too. > **Note:** Formulas are evaluated by a safe parser - Golden does not run arbitrary code. ### Related - [Prepare your data](https://goldenanalytics.com/docs/prepare) - [Visual calculations](https://goldenanalytics.com/docs/visual-calculations) - [Filters](https://goldenanalytics.com/docs/filters) - [Number and currency formatting](https://goldenanalytics.com/docs/number-and-currency-formatting) Source: https://goldenanalytics.com/docs/calculated-fields