Create a Base Formula to Use in Multiple Metrics

If you are creating multiple metrics and each metric uses the same basic calculation as part of its formula, you can save time by creating a base formula to perform the calculation. You can then define additional metrics based on the base formula instead of typing the same calculation multiple times. Base formulas are time-savers because of their reusability and ease of maintenance. When you update a base formula, the changes are automatically reflected in all metrics that use it, ensuring consistency.

For example, you want to create a Profit metric and a Profit Margin metric. You create a Revenue metric which calculates Price x Quantity. You can then define the Profit metric as Revenue - Cost, and the Profit Margin metric as Profit/Revenue. If you decide to change how Revenue is calculated later on, you can make changes to the Revenue metric; all metrics defined using the Revenue metric, including Profit and Profit Margin, will be updated to reflect the change.

You create base formulas using similar steps as you would use to create any other metric. A base formula contains only the metric calculation, without any levels, conditions, transformations, or subtotals. You can also convert a base formula to a metric and a simple metric to a base formula. A metric converted to a base formula does not include any levels, conditions, transformations, formatting, subtotals, and other additional information; it is only the metric calculation.

When creating metrics using the base metric, you include the base formula as part of the metric formula.

• Create a Base Formula

• Create a Metric Built on a Base Formula

• Convert a Base Formula to a Metric

• Convert a Simple Metric to a Base Formula

Create a Base Formula

1. Open the Workstation window.
2. From the File menu, select New Base Formula.
3. If you are connected to multiple environments or projects, select an environment and project. The Base Formula Editor dialog box opens.

4. Click the Name, type a new name, and press Enter.

5. Type a Description for the base formula.

   You can type the metric formula in the Formula pane or use the Objects pane and the Functions pane for a more guided experience. Steps for each method are provided below.

   Use the guided experience

6. Click the Functions pane to search for and select a function.

   • To locate a function, type its name in the Search box.

   • You can also browse for a function by selecting a function category (such as Basic Functions or String Functions) from the drop-down list.

   • When you select a function, its description and syntax display at the bottom of the Functions pane.

   • Double-click a function to add it to the Formula pane. When you add a function, its syntax and description display in a pop-up, as shown below. You can copy and paste the parameter syntax into the Formula pane and edit them. Use the example and the Details link displayed at the bottom of the Functions pane for assistance.

7. Click Objects and use the pane to search for and select the object to use in the expression.

   • To locate an object, type its name in the Search box.

   • You can also browse for an object by using the drop-down list and folders.

   • Double-click an object to add it to the Formula pane.

   Type the metric formula

8. Type the metric formula in the Formula pane on the left. The formula consists of arithmetic operators (+, -, *, and /) and functions such as Average, Rate, and MonthEndDate. The operators and functions are applied to attributes, facts, or metrics. As you type, matching objects, such as functions, attributes, facts, and metrics, display in a drop-down list. To add an object to the metric, do one of the following:
   • To add an object from the list, select the object's name.
   • When you select a function, its syntax and description display in a pop-up. You can copy and paste the parameter syntax into the Formula pane and edit them. Use the example and the Details link displayed at the bottom of the Functions pane for assistance.
   • Click here for examples of metric formulas.

     • Simple metric: Sum(Cost) where Cost is a fact and Sum is a function that calculates the sum of the cost values.

     • Compound metric (combining multiple metrics): Sum(Cost + Profit), where Cost and Profit are metrics and Sum is a function that calculates the sum. In this case, the sum of the Cost metric is added to the sum of the Profit metric.

   Continue creating the base formula

9. Click Validate to check the formula.

10. If it is not a valid expression, check and repair the formula's syntax.

11. Click OK to save the formula. Base formulas focus on calculation; they do not include levels, conditions, transformations, formatting, or other options such as subtotals. This means that the Breakdown, Formatting, and Options tabs are not available when you create a base formula.

12. Click Save.

13. Once you have created a base formula, you can use it to create other metrics, as detailed below.

Create a Metric Built on a Base Formula

1. Open the Workstation window.
2. From the File menu, select New Metric.
3. If you are connected to multiple environments or projects, select an environment and project. The Metric Editor dialog box opens.

4. Click the metric Name, type a new name, and press Enter.

5. Choose a data type for the metric in the Data Type drop-down list, or leave it as Default.

   • Some data types allow you to format them. For example, if you choose Binary, you can set the Byte Length; if you select Decimal you can set the Precision and Scale. When formatting is available, the More Settings (three dots) icon becomes available. Click it to set the formatting.

     

6. Type a Description for the metric.

7. You can type the metric formula in the Formula pane or use the Objects pane and the Functions pane for a more guided experience, as described in Create a Stand-Alone Metric. The base formula is the object that the function uses.

   • For example, you can define a Profit metric as [{Revenue Base Formula} - [{Cost Base Formula}].

   • You can also use a base formula and apply levels, conditions, and transformations to it. You can create last year's revenue, last month's revenue, and last week's revenue, all using the same revenue base formula.

8. Click Validate to check the formula and automatically fill in some parameters and the report level. In the example below, information about the level displays between curly braces {}. This indicates that the formula uses the report level with standard filtering and standard aggregation.

9. You can edit the parameters that have been added. For example, you added the NTile function and the Revenue metric then clicked Validate. The parameters for the number of tiles and whether the values are sorted in ascending or descending order are automatically added, as shown below. You can edit the number of tiles and change Ascending to be false.

10. If it is not a valid expression, check and repair the formula's syntax.

11. Click OK to save the formula and enable the other tabs such as Breakdown.

12. You can apply levels, conditions, transformations, formatting, and additional options.

13. Click Save.

Once you have created a metric, you can add the metric to a report, document, or dashboard to analyze your data. For steps, see Create and Edit Reports, Document Authoring, or Create a Blank Dashboard. You also use metrics when creating Create Filters and prompts (see Create and Edit Object Prompts for Reports and Create Value Prompts).

Convert a Base Formula to a Metric

1. Open the Workstation window.

2. Find the base formula to convert and double-click it. For example, the base formula shown below calculates profit from the Revenue and Cost facts.

   

3. From the Save menu, select Save As Metric.

4. Type the new metric's name in the Save As box and click Save. The report level is added to the metric and the tabs are available for you to modify the metric.

   

5. You can apply levels, conditions, transformations, formatting, and additional options.

6. Save the changes.

Convert a Simple Metric to a Base Formula

A simple metric uses basic functions and aggregation functions. It can contain levels, conditions, transformations, formatting, subtotals, and other additional information, which are automatically removed when you save it as a base formula.

1. Open the Workstation window.

2. Find the metric to convert and double-click it.

3. From the Save menu, select Save As Base Formula.

4. Click Yes to save the metric as a base formula and remove any levels, conditions, transformations, formatting, and so on.

5. Type the new metric's name in the Save As box and click Save. The base formula (that is, only the metric calculation) displays.