The system processes currencies and units after the standard aggregation and before the exception aggregation (see Aggregation Steps). The first currency aggregation takes place after converting the currency. This means that values with different currencies or units are aggregated in a result value. The currency aggregation is repeated for all further aggregation steps, that is, it is executed repeatedly for multiple aggregation steps.
The following example shows some aggregationsto the data records V1 and V2 where data record V2 consists of values with different currencies.
Data record V1 contains values with the same currency:
V1 = { (Luke, 10€), (Obi-Wan, 12€), (Anakin, 8€) }
This produces the following results for different aggregations:
Aggregation |
Explanation |
---|---|
SUM(V1) = 30€ |
Calculates the total of all values 10€ + 12€ + 8€ |
CNT(V1) = 3 |
Calculates a counter of {Luke, Obi-Wan, Anakin} |
AVG(V1)= 10€ |
Calculated as division from SUM(V1)/CNT(V1) |
MIN(V1) = 8€ |
Calculated as minimum value of all values {10€, 12€, 8€} |
MAX(V1) = 12€ |
Calculated as maximum value of all values {10€, 12€, 8€} |
LAST(V1) = 8€ |
Calculated as the last value of all members of the expression {Luke, Obi-Wan, Anakin} including the alphabetical sequence. This is therefore the value "Obi-Wan". |
Data record V2 contains values with different currencies:
V2 = { (Luke, 10€), (Leia, 15$) }
This produces the following results for different aggregations:
Aggregation |
Explanation |
---|---|
SUM(V2) = * |
Since the values show different currencies, the total cannot be calculated correctly. The result is '*'. This is the symbol for mixed currencies or units. |
CNT(V2) = 2 |
Calculated as counter of {Luke, Leia} irrespective of currencies. |
AVG(V2) = * |
As this is to be calculated as a division of SUM(V2)/CNT(V2) and the result of the aggregation SUM(V2) = *, the average is also *. |
However, you should note that not all aggregation types for different currencies or units cause an uncertain result ('*').
The different aggregation types differ in the following way with regard to the currencies and units:
Aggregation types that are completely independent from currencies or units are CNT and CN0. Neither the input nor the output of these aggregation types includes currencies or units.
An aggregation type that includes currencies and units in their input, but where no currency or unit is assigned to the result, is VAR. The result has no currency or unit, but if the variance of two different currencies or units is to be calculated, the result is '*'.
All other aggregation types include currencies and units both in their input and output. The result of these aggregation types refers to currencies and units.
To include aggregation types, currencies, and units (second and third), there are rules for the case that currencies or units appear mixed in the aggregation result or are determined uniquely.
The system first implements the following aggregation rules:
If different values that do not equal 0 are aggregated with different currencies or units, the result currency or unit is undetermined ('*'). If there is only one currency or unit, this is the one of the result.
If the data record contains values that are equal to 0, the system only includes the currencies or units of those key figure values that do not equal 0. It is determined from this if the currency or unit is unique or not.
If the data record only contains values that are equal to 0, the system takes on one of the currencies or units that appear in the result.
During the currency aggregation of aggregation types that are not MIN and MAX, the OLAP processor establishes two sets of currencies or units: One contains all currencies or units of key figure values that do no equal = (set CUP) and the other contains all currencies or units of key figure values that equal 0 (set CU0). If the CUP set only contains one entry, the result is unique. If the CUP contains more than one entry, the result is '*'. If the CUP set is empty, the result has a currency or unit from CU0.
Currency aggregation with aggregation type SUM
V2 = { (Luke, 10€), (Leia, 15$) } SUM(V2) = * V3 = { (Leia, 15$), (Palpatine, 0€)} SUM(V2) = 15$ V4 = { (Palpatine, 0€), (Boba, 0$) } SUM(V2) = 0 ? (? could be either € or $ depending on the system) |
In addition to the rules named above, others apply to the Aggregation Types MIN and MAX. For MIN and MAX the system does not establish two sets of currencies or units, but three. The first set contains all currencies or units of key figure values that are equal to 0 (corresponding to CU0). The second set contains all currencies or units of positive key figure values (CUP) and the third set contains all currencies or units of negative key figure values (CUN).
The following rules apply to the aggregation type MIN:
If different values smaller than 0 are aggregated with different currencies or units, the result currency or unit is undetermined ('*'). If there is only one currency or unit, this is the one of the result.
If there is no key figure value smaller than 0 in the data record, the system includes the currencies or units of the key figure values that are equal to 0. If there is only one currency or unit, this is the one of the result, otherwise the result currency or unit is undefined ('*').
If there are no key figure values smaller than or equal to 0 in the data record, the system checks is all positive key figure values have the same currency or unit and uses this for the result. If this is not the case, the result currency or unit is undefined ('*').
Currency aggregation with aggregation type MIN
V5 = { (Han Solo, -12€), (Chewbacca, 0£), (Leia, 15$) } MIN(V5) = -12€ V6 = { (Yoda, -61$), (Han Solo, -12€), (Palpatine, 0€) } MIN(V6) = * V7 = { (Palpatine, 0€), (Leia, 15$), (R2D2, 28€) } MIN(V7) = 0€ |
In comparison to the aggregation type MIN, the system applies the same rules for the aggregation type MAX but for the subsets or key figure values larger, equal to, and smaller than 0 in reverse order from positive to negative values. The rules are as follows:
If different values greater than 0 are aggregated with different currencies or units, the result currency or unit is undetermined ('*'). If there is only one currency or unit, this is the one of the result.
If there is no key figure value greater than 0 in the data record, the system includes the currencies or units of the key figure values that are equal to 0. If there is only one currency or unit, this is the one of the result, otherwise the result currency or unit is undefined ('*').
If there are no key figure values greater than or equal to 0 in the data record, the system checks is all negative key figure values have the same currency or unit and uses this for the result. If this is not the case, the result currency or unit is undefined ('*').
Currency aggregation with aggregation type MAX
V5 = { (Han Solo, -12€), (Chewbacca, 0£), (Leia, 15$) } MAX(V5) = 15$ V6 = { (Yoda, -61$), (Han Solo, -12€), (Palpatine, 0€) } MAX(V6) = 0€ V7 = { (Palpatine, 0€), (Leia, 15$), (R2D2, 28€) } MAX(V7) = * |