Documentation

  1. Home
  2. Docs
  3. Documentation
  4. Exploring Your Data
  5. Adding a Formula

Adding a Formula

Note: You can see a list of all available formula functions here.

In some cases, you might need to display a column with custom calculations, or do advanced data filtering based on a formula. In these cases, you can use the formula icon on the right side of the search box to add a data field or a data filter.

Choosing a dataset before adding a formula

Note that in order to add a formula to your search, you need to either select a dataset manually from the left side datasets list, or to ask a question that auto detects a dataset. After the dataset is chosen manually or automatically, the add formula icon will be active.

Formula icon

Adding a data field using a formula

This is the same as adding a calculated field in the dataset. However, formulas are used by the same user only and not shared with the rest of the organization. You do not need a permission to add your own formulas, while you would need permission to manage the column properties of the dataset.

To add a formula for a data field, do the following

1- Click on the formula icon on the right side of the search bar

2- In the formula edit popup, select the “Data field” option, which would be selected by default

3- Fill the formula name (the name of hte new field)

4- In the text area, write the formula used to calculate the value of the field. 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.

field formula

5- You can fill the following optional fields:

  • Data Type: This specifies the type of data output of the formula.
  • Field Type: This can be either a metric (number) or dimension (typically text, date or geo fields)
  • Default Aggregate: This can only be set for metric fields, to specify the default aggregation in case it is not explicitly mentioned in your search. Note that if your formula code already has an aggregation ,it cannot have a default aggregate . For example, a formula field that calculates profit margin as sum(profit)/sum(revenue).

Note that AnswerDock will auto-detect the above properties if they are not manually set by the user.

5- Click validate to validate the formula

6- Click Save.

7- The formula will show in the search bar and the question will be automatically processed and the answer reflected on the chart.

field formula search

Nested Formulas

AnswerDock supports defining nested formulas,  allowing the user to reference a formula within another formula.

Each defined formula 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 formulas:

Formula 1 (Profit) = Revenue – Cost

Formula 2 (Profit per thousand sessions) : 1000*Profit/(usersessions)

There is not limit to the nesting depth (number of levels of formulas referencing others). AnswerDock performs dependency checks to ensure formulas remain valid. If a formula is used in another one, then you will not be able to delete it.

Adding a data filter using a formula

This functionality allows you to create your custom logic for limiting the results of your search. While the search bar understands almost all filter clauses directly and supports compound filters, it is sometimes useful to be able to write custom logic for very advanced filter operations.

To add a formula for a data filter, do the following

1- Click on the formula icon on the right side of the search bar

2- In the formula edit popup, select the “Data Filter” option.

3- Fill the formula name (the name of the new filter)

4- In the text area, write the formula used for the filter logic. Below is an example:

filter formula

5- Click Validate. Note that the outcome of your filter should either be a 0 or 1, and AnswerDock will show an error message if that is not the case.

6- Click Save.

7- The filter will show in the search bar and the question will be automatically processed and the answer reflected on the chart.

Editing a formula

To edit the formula, you just need to click on the formula in the search bar, this will open the formula edit popup.

Deleting a formula

To delete a formula, just delete the word from the search bar.