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.
CUMULATE accumulates across time not a list.
Try using RANKCUMULATE().
=RANKCUMULATE(Value to be accumulated,1)0
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.
Just wanted to let you know that Cumulate() can work with lists as well.
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.
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.1
You got it bud, we are all in the same boat (learning every day);1