Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

@Jared Dolich 

Thank you so much.  This was the solution I needed.  I missed the comment about using the same line item for the OFFSET.  That was it.  

Appreciate all your help today.  I will reach out again if I have additional questions.

Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

Hello @Jared Dolich ,

I'm working on a different formula in the same module.  Let me know if I should move this to a different post but it's related so I'm posting here.

I have three line items - 3% Escalation, Manual Input, Final.  

The 3% Escalation is the formula you assisted with earlier.  The Manual Input line is to enter a different number if we don't want to use the 3% escalation.  The Final Line item is where it picks one or the other.

I need some assistance in figuring out the Final Formula for the forecast years.

For FY 2020 - We only want to use the Input Line.  

For FY 2021 and FY 2022 - We want the formula to default to using 3% escalation unless the Input line is populated or we have identified that that item should be projected at $0.  

Is there any other way to incorporate this without having to setup a boolean for which line items we want to leave at $0 vs. which ones we want to use 3% vs. which ones to use manual input?  

The reason we would want to leave certain line items at $0 is because they only occurred once or twice a year so no trends.  

Here are the screenshots for the formula that I have been attempting but it is not accomplishing the results I would like to see:

svbhagat_0-1577748626297.png 

svbhagat_2-1577749150281.png

Here is a screenshot of how I would like to see the results:

svbhagat_1-1577749072956.png

Thank you

 

 

Highlighted
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

@svbhagat 

Awesome question.

This can be done with complex nested if statements but the real answer is what you mentioned early on.

Create two system modules.

  1. One for the Booleans that meet your conditions for rate increases.
  2. One for the rate increases per year (which will also contain the alternative (0%) if one of your Booleans is tripped)

You will make your life SO much easier and also for those that need to support whatever it is  you build if you follow the DISCO method.

It's worth it. Trust me - I used complex, nested, if statements for so long. Once I went through the cert. level 2 course, it changed my perspective completely. 

There's no going back...

 

If you want, we can carry this conversation offline. We can link up on LinkedIn - just search for Jared Dolich and I'll give you my contact info.

Jared Dolich - Retail, Wholesale, eCommerce Analyst
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

Hello @Jared Dolich 

Thank you for your help.  I agree that building out the System Modules would be the right step but we are in a time crunch and I may not have the time and resources to complete that at this time.  Therefore I was looking for a formula I could apply to the existing module.  

 

I look forward to connecting with you soon.  Thanks for your help.