Creating and Editing Formulas

You can create formulas for your widgets directly from the data browser when you add a field or you can edit the calculations for existing fields.
Note: The Rdouble and Rint functions are not currently supported.
To create or edit a formula:
  1. Click Dashboards tab, the Dashboards tab, and select a dashboard from the list.
  2. Create or edit a widget in the Widget Designer.
    For more information, see Creating Widgets or Editing Widgets.
  3. In the data panel on the left, do one of these:
    • To add a new field based on a formula, click Add button, the Add button, and then click Create Formula button, the Create Formula button, in the top-left corner of the data browser dialog box.
    • To change the formula used for an existing field, hover over the field and click Edit Formula button, the Edit Formula button.
  4. Define the formula in the Formula Editor.
    Formula Editor
    1. In the Functions tab, select the function that you want to use.
      You can hover over a function in the list to see the function description and the required syntax.

      The available functions are grouped into four categories: Statistical, Math, Date & Time, and Other. To go to a specific category, click Jump To button, the Jump To button, and select a category name.

    2. In the Data Browser tab, select one or more data fields to include them in the formula.

      Each field has a default behavior that depends on the type of data included in the field and the type of data required by the widget. For example, when the widget requires numeric values, the default aggregation method for numeric fields is Sum. You can see information about the default behavior of a field by hovering over the field.

      To change the default behavior of a field, hover over the field and click More.... You can select a different aggregation method, apply filters, or set a different time period for date fields.

      You can also use arithmetic operators to add, subtract, multiply, or divide the values in the data fields without selecting a function. For example, the formula [Total Printed Pages]*[Total Printed Copies] multiplies the number of printed pages by the number of printed copies.

    3. Enter the required parts of the formula according to the function syntax.
      For more information and examples of formulas, see Building Formulas with Functions, Creating Formulas Based on Filters, and Using Conditional Statements.
    4. To change the default behavior of a data field inside the formula, right-click the field and select one of the options available in the menu.
      • Rename: Enter a different name for the field.

        For example, you can specify a name that represents the expected result or that includes any filters that you applied.

      • Filter: Add filters to the field.

        For example, in the formula SUM([Printed Impressions]), you can add a ranking filter for the Printed Impressions field to calculate the sum of printed impressions using only the top 10 values.

      • Type: Change the default aggregation method.

        For example, you can switch from Sum to Average.

    5. Click OK.
    A large version of the Formula Editor is also available. Click Expand button, the Expand button, in the top-right corner of the dialog box.Formula Editor - Large version
Parent topic: Using Formulas