Skip to main content

 

Description

Cumulates each item of a list using its ordering

 

Syntax

CUMULATE(Number, Cumulated Dimension n, Group Dimension] ], Aggregation])

  • first parameter Number can be a Metric or a List Property

  • Cumulated Dimension, list according to which cumulation is made, should be one of the Dimension of the first parameter

  • Group Dimension is a list that resets the accumulation (optional)

  • Aggregation SUM, AVG, MIN, MAX (optional)

 

Return type

number

 

Examples

Case

Results

Return Type

CUMULATE(10, 'Month')

cumulates 10 on each month starting from the first period of the calendar (ex. Jan 20: 10, Feb 20: 20, Mar 20: 30, etc.)

number

CUMULATE('Quantity Sold', 'Month')

cumulates the metric Quantity Sold (each month is added to the sum of the previous months whatever the year).

number

CUMULATE('Quantity Sold', 'Month', 'Month'.'Year')

cumulates the metric Quantity Sold (each month is added to the sum of the previous months but restart at each change of year).

number

CUMULATE('Quantity Sold', 'Month', 'Month'.'Year', AVG)

averages the metric Quantity Sold (each month is added to the sum of the previous months but restart at each change of year).

number

 

Use ON operator with CUMULATE

You can use the ON operator to change the order of accumulation in the CUMULATE function. An example of how to do this is is provided below, this Metric is dimensioned by Vacuum Cleaner and represents inventory: 
 

  CD001 Inventory
Vacuum Cleaner A 30
Vacuum Cleaner B 48
Vacuum Cleaner C 24

 

If you were to create a new Metric dimensioned by the Vacuum Cleaner Dimension, you could accumulate the values by using the CUMULATE function. The default formula looks like this:

CUMULATE(CDO01 Inventory, ‘Vacuum Cleaner’)

 

And the output looks like:

However, you can change the order in which the values are accumulated by referencing an integer Property in the formula. Check out the Priority Property in the Vacuum Cleaner Dimension below. 

You can change the order of the accumulation by using the ON operator as follows:

CUMULATE(CDO01 Inventory, ‘Vacuum Cleaner’ on ‘Vacuum Cleaner’.Priority) 

 

Remember to include the ON operator in the second argument of the function. This helps you determine the closing balance after fulfilling sales orders.
 

 

See the output below:

The inventory is now being accumulated in the order of the Priority Property.

Excel equivalent: None

See also: DECUMULATE

Ask your question here!

Terms & ConditionsCookie settings