Using Formulas
Using Formulas
Formulas provide the ability to analyze your data in powerful ways.
Using the formula editor
The formula editor is located at the top of a worksheet within a workbook. Select a column on a worksheet and apply a formula with supported Datameer functions and operators.
Formulas can be added manually or using Datameer's Formula Builder. An auto-complete feature gives you the ability to visualize and complete formulas efficiently.
As of Datameer X 7.2
The formula editor has multi-line functionality. Add more than one line when writing a formula so that it is easier to both read and write long and/or nested formulas.
Add additional lines by pressing Enter on your keyboard while in the editor. Submit your formulas by clicking the checkbox on the right of the editor or using the keyboard shortcut ⌘-Enter (mac) and Ctrl-Enter (win).
The multi-line editor can be expanded or collapsed by clicking and holding the edge of the formula bar and dragging your mouse.
Referencing worksheets and columns in the formula editor
A formula can only reference columns from it's own worksheet and one other worksheet in the workbook. Referencing columns from different worksheets is possible after a join sheet has first been created.
| Syntax for referencing a worksheet and column | ||
| Syntax | Example | |
|---|---|---|
| Worksheet | # | #apache_log |
| Column | ! | !remoteUser |
| Worksheet and column | #apache_log!remoteUser | |
Writing a function in the formula editor
Datameer's supported functions.
Enter a function name into the formula editor (case insensitive) with variables separated by a semicolon within parentheses.
Example: | Result explained |
|---|---|
| GE(#apache_log!status;400) | The Greater Than or Equal To function is being used comparing the "status" column from the "apache_log" worksheet |
Writing nested functions in the formula editor
From the formula editor, you can enter functions and operators within functions.
| Example | Results explained |
|---|---|
| IF(EQUALS(#apache_log!status;400);"Bad Request";"other") | The IF function is being used with the Equals function as one of the variables. |
Creating formulas using the formula builder
You can create formulas on worksheets that are editable. When you click the data area of a column that has a formula associated with it, the formula displays above the workbook in the Fx bar. You can also create a new sheet and create formulas that reference fields from other sheets. Click the Fx button on the formula line to display the formula builder. (As of Datameer X 7.2, the formula builder is located in the worksheet inspector.)
To create a formula using the Formula Builder:
- Click the Fx button on the formula line to access the formula builder.
- Select the categories in the left column to choose the type of function.
- Choose a function from the list on the right.
- Enter the argument or arguments shown for that function. You can click a sheet and select a column.
- Click the Plus icon to enter additional arguments (if supported).
- Click OK.
The resulting function displays next to the fx icon. To learn more, see using the Formula Builder.
To use operators in a formula:
- Click the data area in a column and the current formula if one exists displays above the workbook.
- Create or edit the formula using operators such as < or > or <= and press Enter.
To use regular expressions in a formula:
- Click the data area in a column and the current formula if one exists displays above the workbook.
- Create or edit the formula using regular expressions press Enter.
See Using Regular Expressions to see some examples. Datameer X offers an API to extend the built-in formulas. See the Developer's Guide to learn more.
Editing formulas
To edit formulas:
- Click the data area in a column and the current formula displays above the workbook.
- Edit the formula and press Enter.