Menu

Analyze and compare data through calculated fields

  • Updated
Analyze and compare data through calculated fields

Using ARW report data, you can calculate values based on existing data. For example, you can calculate the difference between this year sales and last year sales, and include that value as a new field on your report

If you are familiar with Microsoft Excel, you will notice that the calculations are very similar to those you use in that application.

Note that calculated fields are different from Totals and Subtotals, which you can add to your report without creating a separate field. Totals and Subtotals are calculated based on a single type of data, for example, Sum all year to date sales. Calculated fields are based on the comparison of two types of data, for example, show the difference between last year sales and this year sales.

Data fields that you use for the calculation do not need to be visible on the report, but they do need to be included in the list of fields selected for the report.

Note:
Since these fields are calculated. and therefore created in real time, you cannot sort your report by calculated fields.

  1. Create a report with two types of data that you would like to compare, such as this years sales and last years sales. For example, Sample ARW: Simple customer YTD Sales Report.
  2. Click Edit, and then click the Layout tab.
  3. In the upper-left panel, expand Fields.

    Bound fields are those that you have added to the report directly. Calculated fields are those based on the comparison of two or more existing bound fields.

  4. Right-click Calculated, and then click Add.

    A new calculated field, Field1 appears.

  5. Set the properties of that field, including name, type, and formula.
    1. For best practice, name your new field so you can easily identify it later. At the bottom of the Report Layout in the Designer section, under Design, set the Name property. For example. YTDSalesDiff.
    2. Set the type of calculation that will be performed by this field. Under Misc, set the Field Type property to Double. Other Field TypesClosed
      • String: used to concatenate multiple fields. Can also be used to add a value to a field. For example, adding a prefix to item numbers or combining the Product and Description fields.
      • Int32 (Integer): whole numbers only.
      • Float: less precise decimal value.
      • Double: numbers with decimal values.
      • Boolean: true or false only. Typically used to indicate if the value in one field is equal to the value in another field.
    3. In the Formula value, type the formula for the field, calculating the difference between this years sales and last. You must use the actual data field names.

      =CUSTOMER_YTDSales-CUSTOMER_LastYearsalesYTD

      Tip
      If you do not know the exact name of the fields that you want to use in your calculation, click the field in the report, and then right-click to copy the name from the DataField property. Click your new calculated field again, and paste the name into your Formula.

    4. In the Appearance section, set the Output Format to Currency, as described in Assign the correct data format in an ARW report.
  6. Click and drag the field you just created YTDSalesDiff into the Detail section of your report. Once on the report, you can click and drag to fine tune placement.

  7. You may also use the toolbar to change the font color and layout to help it stand out.
  8. To see the results, click Run Preview.

    Tip
    You can add a subtotal to the calculated field. See Add subtotals to an ARW report.

Related articles