## Use of CUMULATE function

Regular Contributor

## Use of CUMULATE function

Hi I'm trying to solve for the following:

The screenshot below shows the two columns am trying to accumulate, Rows are the A1 Accounts dimension.

The next screenshot shows I have a column for Country and is list formatted with G2 Country. Now, if I am trying to sum for each country (e.g. US, Canada, UK etc.) couldn't I use the CUMULATE function with the following manner: CUMULATE(Exclude, FALSE, Country)? Exclude are the values I'm trying to sum while the FALSE omits the Boolean reset as recommended in the Technical documentation in this method and Country is the list formatted line item I'm trying to sum against. However, I'm getting an error as shown in the last screenshot below.

Screenshot showing the error message saying that Country is not a hierarchy. What am I doing wrong? Am I taking the right approach or there are other ways of solving for this? Your advice is much appreciated.

Kind regards,

Clarence

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Boss

## Re: Use of CUMULATE function

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE()

=RANKCUMULATE(Value to be accumulated,1)

Chris
HeathcoteAndHerran.com
Regular Contributor

## Re: Use of CUMULATE function

I'll check off your proposed solution as a solution but I needed some additional arguments to get it to work as follows: RANKCUMULATE(Exclude, 1, ASCENDING, TRUE, Country).

"Exclude" are the values to be summed.

TRUE is the Boolean flag to include all elements in the ranking group I believe.

"Country" is the ranking group.

Kind regards,

Clarence

6 REPLIES 6
Community Boss

## Re: Use of CUMULATE function

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE()

=RANKCUMULATE(Value to be accumulated,1)

Chris
HeathcoteAndHerran.com
Regular Contributor

## Re: Use of CUMULATE function

I'll check off your proposed solution as a solution but I needed some additional arguments to get it to work as follows: RANKCUMULATE(Exclude, 1, ASCENDING, TRUE, Country).

"Exclude" are the values to be summed.

TRUE is the Boolean flag to include all elements in the ranking group I believe.

"Country" is the ranking group.

Kind regards,

Clarence

Moderator

## Re: Use of CUMULATE function

Just wanted to let you know that Cumulate() can work with lists as well.

Rob

Regular Contributor

## Re: Use of CUMULATE function

Thanks. It was my impression that CUMULATE would work as well but unfortunately didn't (at least in my construct) hence the subject of the email in the first place. Was only able to solve with RANKCUMULATE.

Kind regards,

Clarence

Community Boss

## Re: Use of CUMULATE function

I clearly didnt know that and can already see a few use cases where this would be very helpful.

Always learning, always striving.

Chris
HeathcoteAndHerran.com
Moderator

## Re: Use of CUMULATE function

You got it bud, we are all in the same boat (learning every day);