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

Accepted answers

ChrisAHeathcote

@ClarenceAndre

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE().

=RANKCUMULATE(Value to be accumulated,1)

ClarenceAndre_*44012

Hi @ChrisAHeathcote

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

All comments

ChrisAHeathcote

@ClarenceAndre

CUMULATE accumulates across time not a list.

Try using RANKCUMULATE().

=RANKCUMULATE(Value to be accumulated,1)

ClarenceAndre_*44012

Hi @ChrisAHeathcote

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

rob_marshall

@ChrisAHeathcote

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

Rob

ClarenceAndre_*44012

Hi @rob_marshall

@ChrisAHeathcote

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

ChrisAHeathcote

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

rob_marshall

@ChrisAHeathcote

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

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…