help with formula

neg177
Contributor

help with formula

Hi,

I'm trying to create a function that divides two line items, but the second line item needs to be the value of the 8th month from the first line item. This is the function that I created. 

 

'F01 Funding Breakdown'.Total Funding / IF MONTH(START()) = 1 THEN OFFSET('F01 Funding Breakdown'.Scaled New Originations, 8, 0) ELSE 0

 

I'm still not getting what I want with this function. please advise. 

 

Thank you 

 

4 REPLIES 4
JaredDolich
Master Anaplanner/Community Boss

Re: help with formula

@neg177 

You have the best questions. Here's how I would approach this.

Instead of using the month(start()) equation which would have to be calculated at every cell, try using a system module that indicates which month is the first month.

You can set up a module that only uses the model calendar. Create a line item that is Boolean Formatted. Set the formula to find the first month. Call it "First Month". Then use that in your Line Item 2 formula, like this. The "8", by the way, can be a driver you set in another system module in case you need it to be more dynamic.

Offset003.png


Jared Dolich
neg177
Contributor

Re: help with formula

Wait Im kind of confused what the Boolean Line Item would be. Because I would want the formula to Divide the first Line Item by the Second Line Item, but the second line item needs to be the value eight months later.  So for example If it was February, I would need to divide by the value in October. If it was March, I would need to divide by November's value...and so forth 

JaredDolich
Master Anaplanner/Community Boss

Re: help with formula

@neg177 

Cool. Like this? I would just create a third line item. It's a lot easier.

Offset004.png


Jared Dolich
neg177
Contributor

Re: help with formula

Got it, I'm testing it out now. Thank you!