Create Calculated Field or Measure

Overview

The Ad-Hoc Editor allows custom calculations to be added by applying formulas to a view’s existing Field or Measure.   Numerous functions and operators are available for selection when building your custom calculation.  Begin by selecting the Create Calculated Field/Measure icon to access the Create Calculated Field or Create Calculated Measure in the Fields / Measures sections respectively.

 

 

 

DOCUMENTATION NOTE:  The following images in this topic will show images  for Measures, however, the same steps apply to creating a custom calculated Field.  

The New Calculated Measure / New Calculated Fields window opens.  This window is where you create a calculated Field or Measure and set its summary function. 

For example, consider the Req and PO View which includes a PO Actual Amount and PO Account Segment Amount.   You can compare values and calculate the difference for each record by creating a custom field that subtracts the Account Segment Amount from the Actual Amount.

In the sample image below, I have created the following formula to identify the difference between the two values:

The result of your calculation may be added to your report.

The custom calculation appears in the Fields or Measures panel as indicated by the custom icons for Calculated Field or Calculated Measureicons.

Right-click to access the context menu that allows you to edit, add as a Column, add as a Field, Create Filter and more.

Key Point

• The following are reserved words and cannot be used as Field Names:  AND, And, and, IN, In, in, NOT, Not, not, OR, Or, or.  Names containing these strings, such as " Not Available" may be used.

Formula Builder Tab

The Formula Builder tab is where you create the formula for your calculated Field or Measure.  This tab includes the following:

• Formula Entry Box - This shows the current formula for calculating your Field or Measure.  You may edit the formula by typing directly into the panel.  You may also add Fields, Measures, and Functions by double-clicking them.  Click the buttons below the Formula field to add operators.  Formulas must use the following syntax:

• • Labels of Fields and Measures must be in double quotes ("):  "PO Status", "PO Name".

  • Text must be in single quotes ('):  '--'

  • Levels must be in single quotes ('):  'ColumnGroup', 'Total'.  

• Show arguments in formula check box:  When this check box is selected, double-clicking a function name in the Functions list adds the full description to the Formula entry box; when the check box is not selected, double-clicking a function name adds only the function.

• Validate button checks the formula for syntax errors, such as missing parentheses or quotes.  Your calculated Field or Measure must be valid before you may create it.  Be aware that syntax validation does not guarantee that your formula will give the results you want, it only verifies syntax.  

Summary Calculation Tab

Summaries show a result applied to all data values.  For example, for a numeric Measure such as Unit Price, the summary value might be the sum of all the Unit Prices; for a text field such as Vendor Name, the summary value might be the count of all vendors.  The Summary tab lets you set the default summary function for your calculated Field or Measure.  

• Calculation List - Displays allowed Summary functions for your calculated Field or Measure.  The available options depend on the data type of the calculation.  Depending on your selection, you may see additional options:

• • Custom Selection - Displays the same options available in the Formula Builder tab, including the Formula entry box, operator buttons, Fields and Measures list, Functions list, and Validate button.  Use these options to build a formula for your custom Summary.  However, for summaries, you are limited to aggregate functions, which is to say, functions that operate on all the values in your Field.  For example, Sum and Mode are valid summary functions, because they use all available Field values to get Summary function, because it operates on a single value at a time.  

  • Weighted Average - Displays a Weighted On drop-down list, which allows you to choose another Field or Measure to use as the weight for the average.

Planning and Testing Calculated Fields and Measures

When you are creating calculated fields and measures, you may need to follow an iterative process: first create your new fields and measures, then view the results, and finally adjust as needed. The following practices can help:

• Reduce the size of your data set — To speed field creation and testing, reduce the size of your working data set in the following ways:

• • Select Sample Data from the drop-down menu in the ad-hoc tool bar.

  • Create one or more Filters. This is especially useful for tables, which display all data by default.

• Limit the number of fields and measures you add to your test reports to restrict the number of summary levels to one or two.

After you have created your Fields and Measures, add them to a Table or Crosstab in your view and verify that they behave as you expect. You may need to edit your fields to get the desired behavior.

In addition, keep the following in mind when creating calculated fields:

• When you create a calculated field, it appears at the bottom of the list.

• Calculated fields that use aggregate functions cannot be added to groups and should not be used as filters.

• By default, the Ad-Hoc Editor supports only two decimal places.

• You can’t delete a calculated field that is in use; these fields have their name in italics.

• • If the calculated field is displayed on your report canvas, remove the calculated field from the report and then delete it from the Data Source Selection panel.

• If the calculated field is the basis of another field, you can’t delete it until you delete the one that builds on it.

 

POWERED BY PROACTIS