C Appendix – Aggregation and Spread Types
Aggregation Types
Table C-1 describes the supported aggregation types.
Note:
If aggregation (any) is trying to aggregate from a partitioned level to a non-partition level (HBI), then the aggregation occurs on the whole fact table. RPASCE does not conduct step up aggregation in batch. Also, RPASCE does not conduct any step up aggregation during the workbook load as well.
Table C-1 Aggregation Types
Aggregation Type | Description | Valid Data Types | Recommended Spread Types | Aggregate over Partition Dim |
---|---|---|---|---|
recalc |
The measure is not aggregated but is recalculated at all aggregated levels through a recalc expression. The passthrough function is not supported with this agg type. Note that RPASCE recommends not using recalc measures on both the left hand (lhs) and the right hand (rhs) of an expression to avoid possible run time failure. Currently, the RPASCE calculation engine cannot do a recalc aggregation while it is inside another measure's recalc aggregation. In the situation where LHS_Recalc = func(RHS_recalc), if the rhs recalc measure has already been aggregated as part of other expressions, this expression has no issue; if the rhs recalc has not been aggregated and must be aggregated now, this evaluation would fail. |
numeric string date Boolean |
none |
Yes |
ambig |
The measure is aggregated by considering the values of all child cells. If all child cells have the same value, the aggregated value is the same as the child cells. Otherwise, it is ambig. |
numeric string date Boolean |
none |
No |
ambig_pop |
The measure is aggregated by considering the values of all populated child cells. If all populated child cells have the same value, the aggregated value is the same as the child cells. Otherwise, it is ambig. |
numeric string date Boolean |
none |
No |
popcount |
The measure is aggregated by counting the number of child cells that are populated (meaning that they have a value different from the NA value for the measure). |
numeric string |
none |
No |
mode |
Picks the most frequently occurring cell value from the base intersection to represent the cell value of the aggregate dimension. |
string |
repl |
No |
mode_pop |
Very similar to the mode agg type, except that it will skip all NA values on the base intersection |
string |
repl |
No |
hybrid |
The measure is aggregated using a specific aggregation type for each hierarchy. This is selected from the valid aggregation types for the measure type. |
numeric string date Boolean |
none |
No |
total |
The measure is aggregated by taking the total (numeric sum) of the values of all child cells at the base intersection. |
numeric |
prop |
Yes |
total_pop |
The measure is aggregated by taking the total (numeric sum) of the values of all populated child cells at the base intersection. |
numeric |
prop_pop |
Yes |
average |
The measure is aggregated by taking the numeric average of the values of all child cells at the base intersection. |
numeric |
prop |
No |
average_pop |
The measure is aggregated by taking the numeric average of the values of all populated child cells at the base intersection. |
numeric |
prop_pop |
No |
max |
The measure is aggregated by taking the maximum of the values of all child cells at the base intersection. |
numeric date |
repl_pop |
Yes |
max_pop |
The measure is aggregated by taking the maximum of the values of all populated child cells at the base intersection. |
numeric date |
repl_pop |
Yes |
min |
The measure is aggregated by taking the minimum of the values of all child cells at the base intersection. Note: For most purposes, the min_pop aggregation type is appropriate because the minimum value of all child values is typically the NA value, which is usually zero. |
numeric date |
repl |
Yes |
min_pop |
The measure is aggregated by taking the minimum of the values of all populated child cells at the base intersection. |
numeric date |
repl_pop |
Yes |
pst |
The measure is aggregated by selecting the first value along the innermost hierarchy and by taking the total of all child values along all others. Note: First only has a meaning in the calendar hierarchy. Therefore, this agg type must only be used for measures whose innermost hierarchy is the calendar hierarchy. |
numeric |
ps |
No |
pet |
The measure is aggregated by selecting the last value along the innermost hierarchy and by taking the total of all child values along all others. Note: Last only has a meaning in the calendar hierarchy. Therefore, this agg type must only be used for measures whose innermost hierarchy is the calendar hierarchy. |
numeric |
pe |
No |
median |
The measure is aggregated as the median value (the middle value when sorted from lowest to highest) of the values of all child cells. |
numeric |
repl |
No |
median_pop |
The measure is aggregated as the median value (the middle value when sorted from lowest to highest) of the values of all populated child cells. |
numeric |
repl_pop |
No |
and |
The measure is aggregated by performing a Boolean And operation on the values of all child cells. |
Boolean |
repl |
Yes |
or |
The measure is aggregated by performing a Boolean Or operation on the values of all child cells. |
Boolean |
repl |
Yes |
Spread Types
Table C-2 describes the supported spread types.
Table C-2 Spread Types
Spread Type | Description | Valid Data Types |
---|---|---|
none |
Values are not spread |
numeric string date Boolean |
repl |
Replicate the value to each cell |
numeric string date Boolean |
repl_pop |
Replicate the value to each populated cell |
numeric string date Boolean |
prop |
Spread value proportionally (previous total non-zero) or evenly (previous total zero) |
numeric |
prop_pop |
Spread value proportionally (previous total non-zero) or evenly (previous total zero) to all populated cells |
numeric |
even |
Spread value evenly |
numeric |
delta |
Increment/decrement each cell evenly. Effectively the even spreading of the change (delta). |
numeric |
ps |
Apply delta to starting period |
numeric |
pe |
Apply delta to ending period |
numeric |
Arithmetic Operators
This section provides information about the arithmetic operators supported in Configuration Tools.
Unary Operators
Table C-3 describes the supported unary arithmetic operators.
Table C-3 Unary Arithmetic Operators
Symbol | Type | Function |
---|---|---|
- |
real |
Negation |
! |
Boolean |
Compliment |
Binary Operators
Table C-4 describes the supported binary arithmetic operators.
Table C-4 Binary Arithmetic Operators
Symbol | Type | Function |
---|---|---|
= |
real Boolean string date |
Assignment |
+ |
real |
Addition |
- |
real |
Subtraction |
* |
real |
Multiplication |
/ |
real |
Division |
&& |
Boolean |
Boolean and |
|| |
Boolean |
Boolean or |
== |
real Boolean string date |
Equality Note: When used to compare two strings, this operator performs a case-insensitive compare. |
!= |
real Boolean string date |
Inequality |
< |
real Boolean string date |
Less than |
<= |
real Boolean string date |
Less than or equal to |
> |
real Boolean string date |
Greater than |
>= |
real Boolean string date |
Greater than or equal to |