Creating Formula Question Regular Contributor

Creating Formula Question

Hi,

Would like to know how to create a Formula with the following structure for a Line Item (1).

Closing Balance of Line Item (2) as of the end of the month, and this will show/happen only on the 10th of every month.

My attempt

END(Line Item (1) OFFSET( Closing Balance  of Line Item (2), ?, ?)

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions  Moderator

Re: Creating Formula Question

Here's are two ideas.

1. I would take a slightly different approach to this that using OFFSET. Instead, I would create a system module that is by day. Add a BOOLEAN line item that checks for the 10th of the month. Add another month period formatted line item that calculates the previous month for each day. Now, the solution. In your planning module, you can create a conditional that looks up the system module for the BOOLEAN. If it's true then LOOKUP the monthly value using the PERIOD value from your system module.
2. Using OFFSET, you can use that same BOOLEAN from solution 1. IF NOT SYS01 Dates.10th of the Month? THEN end of period balance ELSE OFFSET(end of period balance,-10,end of period balance)

Jared Dolich
3 REPLIES 3  Moderator

Re: Creating Formula Question

Here's are two ideas.

1. I would take a slightly different approach to this that using OFFSET. Instead, I would create a system module that is by day. Add a BOOLEAN line item that checks for the 10th of the month. Add another month period formatted line item that calculates the previous month for each day. Now, the solution. In your planning module, you can create a conditional that looks up the system module for the BOOLEAN. If it's true then LOOKUP the monthly value using the PERIOD value from your system module.
2. Using OFFSET, you can use that same BOOLEAN from solution 1. IF NOT SYS01 Dates.10th of the Month? THEN end of period balance ELSE OFFSET(end of period balance,-10,end of period balance)

Jared Dolich Regular Contributor

Re: Creating Formula Question

I'm going to try it.

Thank you Regular Contributor

Re: Creating Formula Question

Hi,

I tried both of the formulas below and Anaplan accepted them, but I'm not getting back any values from my Formula for the 10th of every month.

IF NOT SYS01 Dates.10th of the Month? THEN end of period balance ELSE OFFSET(end of period balance,-10,end of period balance)

IF NOT 'S01 Time Settings'.'10th of every Month?' THEN 0 ELSE 'Securitization Master Acct (x5000 Closing)'[LOOKUP: 'S01 Time Settings'.End of the Month]

Thank you