Alternative to IF Historical Period? formulas

cbrookes

Hi All,

It seems like almost every model I've taken over has relied heavily on something to the effect of:

 

IF Historical Period? THEN Actuals ELSE Forecast

 

I've seen some really **** formulas that have separate business logic for historical and forecast periods nested within the two conditions. Generally, this issue is easy enough to simplify by breaking out the business logic into multiple line items then applying the historical IF/THEN in one line item. 

The thing I've had trouble simplifying with this type of formula is historical overrides. Essentially you need to add something else that checks for overrides before the historical period such as:

 

IF Override? THEN Override Value ELSE IF Historical Period? THEN Actuals ELSE Forecast

 

This can get overly complicated pretty quickly when you have to add in other business logic. 

I'm wondering it there is a better way to approach using Actuals/Snapshots in historical periods than using an IF/THEN statement like above?

andrewtye

Hi @cbrookes 

 

Could you not have the overrides and actuals in almost the same place so that you're passing one value through to this final line item?

So it's LI1: if override? then override else actual, LI2 if historical? then LI1 else forecast

You could also use native versions here... where the final item is if isactualversion() then L1 else 0 so you'd have switchover being used and the formula scope set to actual version only leaving the other ones as inputs.

 

Hope that helps

Andrew