Additional Values

Learn the use of additional values in defining Analyses.

Additional values are used to calculate the value(s) of one or more fields of a record.

To define an additional value, specify the following:

  • Valuename: The additional value is referenced with this name, e. g. in the drop-down list for default values.
  • Valuetype: The type of the additional value. At present, only "computed" additional values are available.
  • Parameter: The formula to calculate the value. Use $<infoAreaID>[<field name or number>] to reference field values.

    Example: $Y1[FreeN1]*2 multiplies field FreeN1 of info area Y1 by 2.

    The field must be part of the underlying query. If the query contains a field multiple times, reference it using $Y1 [0] [FreeN1] and $Y1 [1] [FreeN1].

    For more information on the syntax and the available functions. For further details, see Analysis Functions. For more examples, see Examples.

  • Display-Text: An optional language-dependent text used in the value selection box in Aurea CRM.Web for this additional value. If undefined, Valuename is used.
  • Fixed Stat-Type: Allows you to specify a fixed calculation type ("Sum", "Count", "Average" etc.). This disables the Type selection in Aurea CRM.Web (in the Add a value dialog).
  • Options: Allows you to define options for the additional value. Separate multiple options by semicolon.

    The following options are available:

    • Format: An optional format definition. Use # for the value and ,<number> for the desired number of decimal places. You can also add any desired character you want to display.

      If no formatting is specified, # (i.e. the value) is assumed. If a formatting definition without # is present, the value is always added at the first place.

      Example: Format(#%,1) outputs values with one decimal place and a %-character after the value.

      Note: If a (numeric) value has no Format defined, it is displayed as floating point number with 2 decimal places. To output a value e.g. without decimal places, you have to define it as additional value with Format(#,0) in the Options field.Z-fields (e.g. 2nd currency fields) do not support number formatting.

    In Aurea CRM.Web additional values can be selected as analysis categories and/or analysis value, depending on the following parameter(s) of the Options column:

    • IsColumn: The additional value can be selected as a value.
    • IsCategory: The additional value can be selected as a category.

      You can also specify true or false for IsColumn and IsCategory, e.g. IsColumn(false). Defaults: IsColumn(true) and IsCategory(false). If you specify IsCategory(true), IsColumn is automatically set to false unless explicitly defined otherwise.

      Note: If you use additional values as categories, other options like DoNotSortBars have no effect. The sort order is always determined by the displayed text.
    • DefaultCategory: The additional value is the default category.
    • NoChart: The values for this column are displayed in the list but not in the chart view of the analysis.
    • Cumulate: Displays accumulated values in the results column. The sum total row displays the highest accumulated value (instead of the sum total).
    • ComputeSum: Calculates the sum total row for additional values (for all aggregation types) using the specified formula. (Without ComputeSum the formula is only applied to the result rows and the sum total row is calculated by the analysis engine.)

      The following analysis functions can be used in combination with ComputeSum: $SUM, $COUNT, $COUNTALL, $COUNTX, $STATIC, $SUMCAT, $SUMCATX, $SUMCATDISTINCT, $PERCENT. For further details, see Analysis Functions.

      If you specify ComputeSum for rolling date categories (Example in UPDATE_DEFAULT: DateMonthlyRoll6), each value is considered exactly once.

Additional values can also be used when working with array catalog fields: If an additional value with Option IsCategory returns an array of values, the record is counted for each of these category values (i.e. potentially the record is taken into account by multiple rows).

Example: "Sector" catalog of Company (in BTB): [$!FI[29],$!FI[30],$!FI[31]]

To evaluate additional values once per row:

By default, additional values are evaluated per record. For values where all records in a category should deliver the same result (e.g. number of records or sum of a value across one category), you can change this behavior: Define a fixed aggregation by setting the Fixed Stat-Type to "Static" or specify IsStatic in Options.

Note: Using Fixed Stat-Type "Static" disables the Type selection in Aurea CRM.Web (in the Add a value dialog).
Note: Additional categories (XCategory) are ignored when using static values.