Use of CUMULATE function

ClarenceAndre_*44012

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

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);