Use of CUMULATE function

ClarenceAndre_*44012
ClarenceAndre_*44012
in Modeling

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

Tagged:

Best Answers

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @ClarenceAndre 

    CUMULATE accumulates across time not a list.

    Try using RANKCUMULATE(). 

    =RANKCUMULATE(Value to be accumulated,1)

  • ClarenceAndre_*44012
    ClarenceAndre_*44012
    Answer ✓

    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

Answers

Categories