IF Cumulative for different scenario

Hi ​@Yaejis hope all is well.

• For the first scenario (cumulate in the last period), you can try using the `CUMULATE` function with a `Group Dimension` to reset the accumulation and ensure the total is displayed in the desired period.
• For the second scenario (cumulate in the start period), you might need to use a combination of `CUMULATE` and `SHIFT` to adjust the results to the start period.

These articles below maybe helpful:

Let us know if this helps or not, thanks!

Thank you ​@farhan13 for reviewing!

I am trying to solve the 1st scenario first.

As per your advise, I’ve added ‘CUMULATE’ function with a ‘Group Dimension’. Result is very close, but I would like it to show the cumulated amount only on the date listed in the transaction list ('RSU Accounting Data'.'Term Month'). 

Here’s how I revised so far:

CUMULATE(-IF( Month < 'RSU Accounting Data'.'Term Month' AND Month <= 'RSU Accounting Data'.'Vesting Month',(PRORATA(Month, 'RSU Accounting Data'.'Amortization Start Date', 'RSU Accounting Data'.'Vesting Date'+1) *'RSU Accounting Data'.'Expense Amortized Future' / 'RSU Accounting Data'.'Amortization # of Days' * DAYSINPERIOD(Month))), Month) [BY:'RSU Accounting Data'.RSU#,'RSU Accounting Data'.EE,'RSU Accounting Data'.Vested?,'RSU Accounting Data'.'EE Active? (Incl. Future Term)','RSU Accounting Data'.'Client Grant ID','RSU Accounting Data'.Forfeit?,'RSU Accounting Data'.Type][BY: 'Accounting Status'."Reversal"][BY SUM:SHIFT(Month,1)]

Basically, numbers crossed out with blue line needs to be removed based on the txn list below. 

you can FILTER out you data by adding:

[FILTER: Month = Term Month]

on top of your CUMULATE.

ps: I advise that you calculate everything within your cumulate in a separate metric

Thank you ​@Nathan , it worked! 1st scenario is solved!

For the 2nd scenario, I need total of the metric $620.26 to be allocated all in the terminated month. I added the following green text formula, but did not work. do you have any recommendations?

IF( Month >= 'RSU Accounting Data'.'Term Month' AND Month <= 'RSU Accounting Data'.'Vesting Month' AND 'RSU Accounting Data'.'Rehired EE' = TRUE AND 'RSU Accounting Data'.'Grant Month' < 'RSU Accounting Data'.'Rehire Month',(PRORATA(Month, 'RSU Accounting Data'.'Amortization Start Date', 'RSU Accounting Data'.'Vesting Date'+1) *'RSU Accounting Data'.'Expense Amortized Future' / 'RSU Accounting Data'.'Amortization # of Days' * DAYSINPERIOD(Month))) [BY SUM:'RSU Accounting Data'.RSU#,'RSU Accounting Data'.EE,'RSU Accounting Data'.Vested?,'RSU Accounting Data'.'EE Active? (Incl. Future Term)','RSU Accounting Data'.'Client Grant ID','RSU Accounting Data'.Forfeit?,'RSU Accounting Data'.Type][BY: 'Accounting Status'."Forfeited"][REMOVE: Month] [BY: Month = 'RSU Accounting Data'.'Term Month']

just do [BY:`RSU Accounting Data`.Term] instead of the Month =

Pigment will allocate data to that Period

Hm it did not actually give me any result. I tried in playround as well, but error as below.

I tried removing [REMOVE: Month] purple formula below as well, but same result...

IF( Month >= 'RSU Accounting Data'.'Term Month' AND Month <= 'RSU Accounting Data'.'Vesting Month' AND 'RSU Accounting Data'.'Rehired EE' = TRUE AND 'RSU Accounting Data'.'Grant Month' < 'RSU Accounting Data'.'Rehire Month',(PRORATA(Month, 'RSU Accounting Data'.'Amortization Start Date', 'RSU Accounting Data'.'Vesting Date'+1) *'RSU Accounting Data'.'Expense Amortized Future' / 'RSU Accounting Data'.'Amortization # of Days' * DAYSINPERIOD(Month))) [BY SUM:'RSU Accounting Data'.RSU#,'RSU Accounting Data'.EE,'RSU Accounting Data'.Vested?,'RSU Accounting Data'.'EE Active? (Incl. Future Term)','RSU Accounting Data'.'Client Grant ID','RSU Accounting Data'.Forfeit?,'RSU Accounting Data'.Type][BY: 'Accounting Status'."Forfeited"][REMOVE: Month] [BY:'RSU Accounting Data'.'Term Month']

you need to do this BY before aggregating UP on the transaction data.

So likely before that:

[BY SUM:'RSU Accounting Data'.RSU#,'RSU Accounting Data'.EE,'RSU Accounting Data'.Vested?,'RSU Accounting Data'.'EE Active? (Incl. Future Term)','RSU Accounting Data'.'Client Grant ID','RSU Accounting Data'.Forfeit?,'RSU Accounting Data'.Type]

Sorry could you clarify “before aggregating UP on the transaction data? I tried moving up the BY before aggregating up on the transaction data, but  formula but result was “The formula reached 98 G cells during one of its execution steps, exceeding the maximum size allowed (1 G)”. Did i correctly apply?

IF(
    Month >=  'RSU Accounting Data'.'Term Month' 
    AND Month <= 'RSU Accounting Data'.'Vesting Month'
    AND 'RSU Accounting Data'.'Rehired EE' = TRUE 
    AND 'RSU Accounting Data'.'Grant Month' < 'RSU Accounting Data'.'Rehire Month',

(PRORATA(
 
     Month, 
     'RSU Accounting Data'.'Amortization Start Date', 
     'RSU Accounting Data'.'Vesting Date'+1
 ) 
 
 *'RSU Accounting Data'.'Expense Amortized Future' 
 / 'RSU Accounting Data'.'Amortization # of Days'
 * DAYSINPERIOD(Month)

) 

[BY SUM:'RSU Accounting Data'.RSU#,'RSU Accounting Data'.EE,'RSU Accounting Data'.Vested?,'RSU Accounting Data'.'EE Active? (Incl. Future Term)','RSU Accounting Data'.'Client Grant ID','RSU Accounting Data'.Forfeit?,'RSU Accounting Data'.Type][BY: 'Accounting Status'."Forfeited"]

) 

[REMOVE SUM: Month]
[BY:'RSU Accounting Data'.'Term Month']