Summary: For any dataset, you can add a new field that is calculated based on a formula. This expands the scope of analyses and computations that you can do on your data.
Note: You can see a list of all available formula functions here.
Adding a calculated field to a dataset
To add a field, follow the below steps
1- In the Datasets list page, click edit next to the dataset for which you would like to add a new field
2- Click on the Fields Settings tab
3-Click on “Add new Field”
4-In the popup , fill the name of this new field and in the text area, write the formula used to calculate its value. Note that the autocomplete menu will show you any functions or columns that you can use in the formula. For example, let’s say we would like to add a new field that calculates the costs as : Cost= Sales-Profit. In this case we would add the new field “Cost”, and write its formula. By typing “S” , you can select the Sales field from the menu. The functions used are all based on Mysql native functions. You can see a list of all available functions here.
6- Click validate to validate the formula
7- Click Save.
8- The new field will be added to the end of the list with a formula icon to distinguish it from native columns. You can edit any of the field settings such as the type (metric/dimension), date, Geo and other properties
- Note that if the formula for the calculated field already has an aggregation ,the field cannot have a default aggregate . For example, a field that calculates profit margin as sum(profit)/sum(revenue).
Now the field is ready to be used in the explore page, just like any other column:
Editing and Deleting Calculated Fields
You can edit or delete any calculated field from the fields list page, by clicking on the edit or delete icon. The edit will open the field popup where you will be able to amend its name or formula. The delete icon will delete the field from the dataset
Nested Formulas
AnswerDock supports defining fields based on nested formulas, allowing the user to reference a calculated field within another one.
Each defined calculated field becomes a column that is available to be selected in the formula editor (through the autocomplete menu). For example you can have the following two defined fields:
Field 1 (Profit) = Revenue – Cost
Field 2 (Profit per thousand sessions) : 1000*Profit/(usersessions)
There is not limit to the nesting depth (number of levels of fields referencing others).
Formula Functions
You can see a list of all available functions here.