If statement not reading periods prior to forecast start

Hi All,

I was wondering if someone could help with below.

if statement not reading dates prior to forecast start period.

Forecast start period is Mar -19

Below should return 0 rather than Mar -19, why is the if statement not reading the first part of the statement?

What do I need to change to get it to work?

IF PERIOD(Asset Acquisition date) + Asset life < Cal Start of timeline THEN 0 ELSE Asset life

Asset Acquisition date (20-08-2014) - Date formatted with a date prior to forecast start period March-19

Asset life- Number formatted - 28.63

Cal Start of timeline - Time period - Month formatted - Set to March -19.

The statement is returning 28.63 instead of 0.

Thank you for helping out.

Naveed

Accepted answers

DavidSmith

@NB

Because your time settings only run from 2019, the PERIOD element of the formula cannot return August 2014

Solution:

Set up a time range that runs from as far back as you need to the current timescale start.

This will then create a "superset" of months which will allow the PERIOD to return Aug-14 and the formula should then resolve correctly

I hope this works for you

David

All comments

karank_*4578

Hi @NB

What is your model time settings ?

Please see attached.

Thanks

Model_time_settings_46076.PNG

DavidSmith

@NB

Because your time settings only run from 2019, the PERIOD element of the formula cannot return August 2014

Solution:

Set up a time range that runs from as far back as you need to the current timescale start.

This will then create a "superset" of months which will allow the PERIOD to return Aug-14 and the formula should then resolve correctly

I hope this works for you

David

karank_*4578

Your model cal doesn't go before 2019 which is your your formula PERIOD(Asset Acquisition date) returns nothing .

So your formula is doing this

If <nothing>+28.63 = (nothing as its time formatted) < Mar 19 then 0 else 28.63

If Nothing < mar19 then 0 else 28.63

which is why you are getting 28.63

you can change your formula to check if

if isblank(PERIOD(Asset Acquisition date) + Asset life) or PERIOD(Asset Acquisition date) + Asset life <Cal Start of timeline THEN 0 ELSE Asset life

may be you can put this part as final date to check = (PERIOD(Asset Acquisition date) + Asset life)

if isblank( final date ) or final date < Cal Start of timeline THEN 0 ELSE Asset life

Hope this helps

DavidSmith

@karank

We crossed over on the replies

Check out my post just before

David

Thank you, David.

The solution worked !!

karank_*4578

@DavidSmith yes noticed after i typed my answer (i am a slow typer) .

But its interesting to see how you are suggesting a superset approach vs my formula approach . Are there any pros and cons if we compare these ? .

DavidSmith

@karank

No worries, but in terms of pros and cons

Any additional checks through IF statements and additional line items are going to take longer to process than without, so it is always better to remove formula expressions if possible

Simple is better than Complex!

Also, the time range is not going to add to model size (unless it is used in a module)

David

DavidSmith

Excellent!

Quick Links

Can you help?

Unanswered questions

Rolling forecast
Hi , I have a requirement for my stakeholder to have a medium term forecast (by month for the next 6 months) and a more detailed rolling 15-week sales forecast by day which will then feed into the wider Supply Chain planning processes over time. This needs to be updated by the Sales representants for consolidation by the…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…