Use of CUMULATE function

ClarenceA
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.

TAR02 Account Review showing two cols to sum.jpg

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. 

TAR02 Account Review showing Countries.jpg

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.

 

TAR02 Account Review with error on CUMULATE.jpg

Kind regards,

Clarence

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Use of CUMULATE function

@ClarenceA 

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE()

=RANKCUMULATE(Value to be accumulated,1)

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

ClarenceA
Regular Contributor

Re: Use of CUMULATE function

Hi @ChrisHeathcote 

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

View solution in original post

6 REPLIES 6
ChrisHeathcote
Community Boss

Re: Use of CUMULATE function

@ClarenceA 

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE()

=RANKCUMULATE(Value to be accumulated,1)

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

ClarenceA
Regular Contributor

Re: Use of CUMULATE function

Hi @ChrisHeathcote 

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

View solution in original post

rob_marshall
Moderator

Re: Use of CUMULATE function

@ChrisHeathcote 

 

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

 

2021-05-06_12-15-09.png

 

Rob

ClarenceA
Regular Contributor

Re: Use of CUMULATE function

Hi @rob_marshall 

@ChrisHeathcote 

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

ChrisHeathcote
Community Boss

Re: Use of CUMULATE function

@rob_marshall 

 

Thanks for the heads up.

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

Always learning, always striving.

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
rob_marshall
Moderator

Re: Use of CUMULATE function

@ChrisHeathcote 

 

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