Skip to main content

Add aggregation and formatting to an analytics column

Configuration

Setup of Analytics column definitions is done in FA Back under Preference → content and translations → Analytics+ columns.

The options for aggregation and overriding formatting will, if anything other than the default values are chosen, present new fields on the screen.

add_aggregation_and_formatting_analytics.png

Defining group level aggregation and how to aggregate sub-levels

Select aggregationtype which will define how sub-levels are aggregated to parent levels.

custom_analytics_column_add_aggregation_1.png

Five options are available:

  • Do not show values on group levels - value shown only on position level and not aggregated to any parent level

  • Calculate group level as the direct sum of lower levels - any number value from a child level will be summed to the parent level. The parent level will simply be the sum of its children values.

  • Calculate group level as the weighted average of lower levels - any number value from a child level will be then weighted and summed to the parent level, using each corresponding share of parent as its weight. The weight corresponds to the position level "Share of parent" column

  • Calculate group level as the weighted average using the absolute value of the weights - same as above but where the absolute value of the weight is used. This means that negative positions will have positive in weight.

  • Apply formula also on group level - if formula is applicable, use this on all levels instead of aggregating.

If aggregation type is specified in the previous step, you can select how to aggregate children without a value

custom_analytics_column_add_aggregation_2.png

  1. Aggregate up only if all children have a value - this means that values are NOT aggregated if ANY child is missing a value

  2. Aggregate up with default value 0 if a child doesn't have a value - this means that any missing value is assumed to be zero in the aggregation

It will also allow you to define how aggregated numbers on group level are rounded, which is useful for whole number values and categorized key figures

custom_analytics_column_add_aggregation_3.png

Select how to round values on the group level - if a figure is of Integer type or if a categorized figure is aggregated, this selection defines how to handle rounding of non-existing final values. Default, Round up to nearest value, Round down to nearest value - are all valid selections

Round based on normal rounding rules (half up) OR use the nearest category will round according to normal rounding rules, using half up. Example: 1.51 will be rounded to 2 if the aggregated value is an integer

Always round up OR use the nearest bigger category will always round up to the next closest category value. For example, 1.51 will be rounded to B (2) if the defined values are 1=A, 2=B

Always round down OR use the nearest smaller category will always round down to the next closest category value. For example, 1.51 will be rounded to A (1) if the defined values are 1=A, 2=B

Adding custom formatting

Finally, there is an option to override the formatting of the column in the field Do you want to override formatting? The usage would typically be for Date formatting, Decimal rounding and percentage formatting.

custom_analytics_column_add_aggregation_4.png

If you want to manually override the format of the column select Yes, override format in the dropdown and information about the column as well as formatting options will be presented.

Field

Description

custom_analytics_column_add_aggregation_5.png

Define the format to use - will format numbers according to the specified format. Formatting follows the same logic as defined under Preferences > Content and translations > Date and decimal formats.

Format is defined as a string.

Examples: "0.00" for 2 decimal rounding of numbers, "#,##.00", "0.0%" for percent, or "dd.MM.yy" for Date formatting

custom_analytics_column_add_aggregation_6.png

Value type - this field indicates the return type of the column and cannot be overridden. It is useful when writing formulas to check the output, which is done when a formula is saved. For key figures, the key figure type will be saved to this field.

custom_analytics_column_add_aggregation_7.png

Select how to align the column content in tables - right or left available.

By default, String types are aligned LEFT and other types to the RIGHT

In this section: