1. Calculation functions
  2. Functions
  3. Time aggregation

Values automatically aggregate between different time scales. However, you can also use functions such as MONTHVALUE or YEARVALUE to aggregate values.

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

Values do not aggregate from greater time scales into smaller ones. For example, if x has a time scale of months, and y has a time scale of years, y = x aggregates the monthly values into an annual total. However, x = y returns nothing, as y does not contain a value for each month.

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 Year. The module is named Annual Data, and the line item is named Annual revenue.


FY19FY20FY21FY22
Annual Revenue538,497512,693492,648586,765

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 aggregate values into 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.


Jan 22Feb 22Mar 22Apr 22May 22Jun 22Jul 22Aug 22Sep 22Oct 22Nov 22Dec 22
Monthly Revenue50,89652,16751,35944,69143,08051,27153,45750,84842,15151,38248,07847,385

Years into monthly time scale

'Annual Data'.Annual Revenue

000000000000

Quarter Revenue

QUARTERVALUE(Monthly Revenue)

154,422154,422154,422139,042139,042139,042146,456146,456146,456146,845146,845146,845

Half-year Revenue

HALFYEARVALUE(Monthly Revenue)

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

Annual Revenue

YEARVALUE(Monthly Revenue)

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

Average Revenue per Month

Annual Revenue / 12

48,89748,89748,89748,89748,89748,89748,89748,89748,89748,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 cannot be aggregated into a Time Scale of Month.

Disclaimer

We may update our documentation occasionally, but will only do so in a way that does not negatively affect the features and functionality of the Anaplan service.