Aggregation is gathering values from a smaller time scale to a larger time scale, such as from days to months or years. Values automatically aggregate between different time scales. However, you can also use functions such as MONTHVALUE or YEARVALUE to reference those aggregated values at ‌coarser time scales.

When two line items have a different time scale, and a formula uses both, values aggregate into the larger time scale. For example, days aggregate into months and months into quarters. This aggregation can also skip levels, so days can aggregate into years.

You can also use functions such as HALFYEARVALUE, MONTHVALUE, QUARTERVALUE, WEEKVALUE, YEARVALUE, or SELECT to aggregate values into different time scales.

Examples

The table below contains data with a Time Scale of Month. It also contains a number of line items that show how you can use different functions to reference the aggregated values at different time scales. However, as each cell is calculated separately, it's often more efficient to use a line item with the time scale required.

Here, Summary of the line item Monthly revenue is set to Sum.


Jan 22Feb 22Mar 22Q1 FY22Apr 22May 22Jun 22Q2 FY22H1 FY22Jul 22Aug 22Sep 22Q3 FY22Oct 22Nov 22Dec 22Q4 FY22H2 FY22FY22
Monthly revenue50,89652,16751,359154,42244,69143,08051,271139,042293,46453,45750,84842,151146,45651,38248,07847,385146,845293,301586,765

Quarter revenue

QUARTERVALUE(Monthly Revenue)

154,422154,422154,422
139,042139,042139,042

146,456146,456146,456
146,845146,845146,845


Half-year revenue

HALFYEARVALUE(Monthly Revenue)

293,464293,464293,464
293,464293,464293,464

293,301293,301293,301
293,301293,301293,301


Annual revenue

YEARVALUE(Monthly Revenue)

586,765586,765586,765
586,765586,765586,765

586,765586,765586,765
586,765586,765586,765


Average revenue per month

Annual revenue / 12

48,89748,89748,897
48,89748,89748,897

48,89748,89748,897
48,89748,89748,897


The Years into monthly time scale line item returns a value of 0. This is because values with a Time Scale of Year can't aggregate into a Time Scale of Month.

Note: Values don't aggregate from larger time scales into smaller ones. For example, if Monthly revenue has a time scale of months, and Annual revenue has a time scale of years. Values in the Monthly revenue can aggregate the monthly values into an annual total. However, values in the Annual revenue can't be split into monthly values, as it doesn't contain a value for each month.

Refer to Breakback to learn more about how to distribute a given total value across multiple cells.