SAC aggregation exception on the time dimension: fixing wrong stock and balance totals

SAP Analytics Cloud lets you set two different aggregation rules on a single measure: one for how it aggregates across regular dimensions (entity, product, region) and one for how it aggregates across the time dimension. Most guides cover the first; the second — the time aggregation exception — is what causes the "year-end headcount twelve times too large" error. This guide explains exactly when and why you need a different aggregation rule for time, and how to set it correctly.

Why time is different

Most dimensions are additive in both directions. Revenue for France plus revenue for Germany equals revenue for the region — and revenue for January plus revenue for February equals revenue for Q1. For these measures, SUM works across every dimension including time. But some measures behave differently across the time axis. Headcount at 31 March is not the sum of headcount at 31 January, 28 February and 31 March — it is the closing balance on the last day of the quarter. Cash balance, inventory on hand, total loans outstanding — all of these are point-in-time balances that cannot be summed across periods. This is the exception: the measure aggregates correctly across entities (Paris + London = total), but not across time (March ≠ Jan + Feb + Mar).

The two aggregation settings in SAC

In the SAC Modeler, each measure has two aggregation properties. The default aggregation applies across all non-time dimensions — this is the one most people set. The exception aggregation applies specifically to the time dimension and overrides the default when rolling up periods. For a headcount measure: default aggregation = SUM (Paris + London = total headcount), exception aggregation = LAST (Q1 headcount = March closing headcount, not Jan + Feb + Mar). Both must be set correctly for the measure to produce right totals in every context.

When to use LAST

Set the exception aggregation to LAST for any measure that represents a balance or stock at a point in time: headcount, inventory, cash balance, loan portfolio size, capital balance, any KPI where the year-end figure is the closing value, not the sum of twelve monthly values. LAST returns the value of the last period in the selected time range — so Q1 returns March, H1 returns June, Full Year returns December.

When to use FIRST

FIRST is the mirror of LAST — it returns the value of the first period in the range. It is less common but useful for opening balance reporting: the opening inventory for Q2 is the closing inventory at the end of Q1, which is the first value of Q2. Some banks use FIRST for the opening capital balance in a capital roll-forward schedule.

When to use a calculated measure instead

Some measures look like balances but are actually ratios — the CET1 ratio, the loan-to-value ratio, the gross margin percentage. These should never use LAST or SUM as their time aggregation, because neither produces a meaningful result. The correct approach is to store the numerator and denominator as separate measures (each with the correct exception aggregation), and define the ratio as a calculated measure that divides them at every level. This way, the Q1 CET1 ratio is computed as Q1 CET1 capital (LAST) divided by Q1 total RWA (LAST) — not as some average of three monthly ratios.

The diagnostic: when something is wrong

The tell-tale sign of a missing exception aggregation is a year-end total that is a multiple of the period value — typically 12× for monthly data or 4× for quarterly. If your model shows "year-end headcount: 9,600" and the actual headcount is 800, you are summing twelve monthly closing balances instead of taking the last one. Fix: open the measure in the Modeler, set exception aggregation to LAST, and re-validate. This is different from the standard aggregation error (wrong totals across entities) — it only manifests when you look at annual or quarterly rollups.

Where this fits

Exception aggregation is one part of a broader aggregation discipline. For the full decision guide (SUM vs AVERAGE vs LAST vs COUNT), see choosing the right aggregation in SAC. For the planning model context where these settings matter most (budget, forecast, version management), see the Version dimension in SAC Planning. Our ready-made templates have exception aggregations already set correctly on every measure — so you skip this class of error entirely.