Navigating the Formula window

Follow

You can create calculated metrics and attributes to use in your query or in other calculated elements. These calculated elements are created using a unique formula language in the Formula writing window.

This article instructs you on navigating the Formula writing window and identifying the different options available for creating your formulas. For help creating formulas and functions, see Formula writing resources.

Using the Formula Editor

The most important component in the Formula writing window is the Formula Editor. The Formula Editor is where you will enter or edit your formulas and functions. For information on writing formulas, see Formula writing resources.

When writing formulas, the formula engine will suggest auto-complete matches to what you are typing. Everything entered in the Formula Editor is color-coded.

If your formula is incorrect, an error icon () will appear next to the line containing the error. Information describing the error will appear at the top of the box.

Adding attributes and metrics to your formula

In BIME, you can add attributes and metrics to your formula. Unlike result manipulations, these elements do not need to be currently on your query and can include calculated metrics and attributes.

Note: If you want to manually type in metrics or attributes, you must select BIME's auto-complete suggestion or replicate the exact formatting for metrics and attributes. Calculations will not process with incorrect formatting.
To add metrics or attributes to your formula
  1. Click Select a field underneath Fields.

  2. Select your metric or attribute from the drop-down list.

Any attribute you enter will be inserted in brackets [] and any metric will be inserted in parentheses ().

You can select from any of the following aggregators for a metric:
  • SUM
  • AVG
  • COUNT
  • MIN
  • MAX
  • D_COUNT
  • SUMX
  • MED
  • VARIANCE
  • STD_DEV
For more information on how aggregators calculate your results, see Changing your metric aggregator. You can also use D_COUNT and COUNT to count the number of values within an attribute.

The Fields drop-down list is useful for viewing all the available elements in a dataset. For datasets with several metrics and attributes, it might be better to use the search bar or BIME's auto-complete.

You also have the option of manually typing in metrics and attributes. Please ensure you either select BIME's auto-complete match, or replicate the exact formatting. Metrics must be written with the aggregator first in all caps, and then the metric name in parentheses. Attribute names must be entered inside of brackets. Calculations will not process incorrect formatting.

Adding functions to your formula

You can add functions to your report by clicking the +Add button underneath functions or typing in a function name. Along with elements, BIME will suggest auto-complete results for any functions you type in.
Note: If you would like to manually type in functions, you must select BIME's auto-complete suggestion, or replicate the function in the same formatting. Calculations will not process incorrect formatting.

When you click +Add, you will be redirected to the Functions window. The Functions window is useful if you are not sure which function is applicable to the calculated element you want to create. All functions in the Functions window contain a brief explanation of the functions purpose. For a full list of functions, see Formula writing resources.

If you are looking for a specific function, you can search for functions or filter by function type. There are fourteen function categories you can filter by.

To filter functions
  1. Click the Filter button at the upper left of the Functions window.
  2. Type in a category or select a category from the drop-down list.

  3. Click the x next to the category name to remove the filter.

Calculation options

When you are creating calculated metrics or attributes, there are some additional options you can select from.

You can apply the following two options to your calculated metric or attribute:

  • Compute separately: If your formula will affect your other metrics and attributes. Selecting Compute separately will create a separate SQL query for the calculation and not influence the existing elements.
  • Delegate to database: If you data source is connected to a database, you can sync your calculation to your database by delegating to your database.
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk