Data types and roll-ups

When aggregating data within a model, different indicators require distinct roll-up methods. Blox offers flexibility in defining how indicators are aggregated across time and dimension items.

For example:

• When calculating the Number of Units Sold for all Products, you want to sum the each of the individual Products in the dimension to find the total.
• When calculating the Gross Profit Margin (%) for all Products, you do not want to sum the value of each individual product as this will add to greater than 100%
• When viewing an Annual view of Headcount in the business, it is standard to take the Final value of headcount in the year. Summing each of the months will give you an incorrect value.

This behaviour is controlled in Blox using the Roll-up type property for each indicator. The roll-up type will define how the indicator is aggregated across:

• Time - how will annual or quarterly totals be calculated for the indicator.
• Dimension items - how will totals be calculated when data is input or calculated for each dimension item.

Most indicators which represent numerical values will be of Number type. The aggregation over time and dimensions for a number is:

• Time - Summed across the different time periods to calculate the total
• Dimension items - Summed across each item to calculate the total

Common indicators with the Number roll up type:

• Revenue generated by the organisation
• Total Employee Costs for the business
• The Number of Units sold of a product for an e-commerce business

Some indicators, such as those calculated as percentages or divided values are better represented by averages rather than summed totals. We can use Statistic type for these, which will be aggregated by:

• Time - Averaged (mean) across the different time periods to calculate the total
• Dimension items - Averaged (mean) across each item to calculate the total

Common indicators with the Statistic roll up type:

• Tax rate

When dealing with values that are calculated from other indicators - there are some complex cases where the calculated value of totals may be different to the total of the calculated values. Effectively - the order in which the aggregation occurs can impact the values which.

For these examples we can use the Ratio data type, which will back-calculate totals to ensure consistency across the model.

Common indicators with the Ratio roll up type:

• Price
• Profit margin

Balance type indicators are indicators which have a value which is relevant for a point in time. As a result we do not want to sum them over time, but instead take the current value at the end of the period being aggregated. Therefore Balance indicators are aggregated by:

• Time - The final value in the time period is taken as the aggregated value
• Dimension items - Summed across each item to calculate the total

Common indicators with the Balance roll up type:

• Headcount
• Inventory
• Assets on the balance sheet

The Opening Balance works in the same way as the Balance type, however when aggregating over time it will take the first value in the time period. This is key for indicators which roll forward in time, but represent the initial value.

Common indicators with the Opening Balance roll up type:

• Opening cash balance
• Starting headcount