Forecast Analysis

[Seba]

I am creating a workflow to compare the actuals of the month versus the forecast. At this point, I generated a formula to identify the "Month Type" for each period. However, the formula is not working for the lines corresponding to the period 01-2024, as these should be labeled as "Pending"

Any ideas?

Thanks and Happy thanksgiving!

[AndrewDMerrill]

The issue is that you are working with string fields. I recommend converting to DateTime/Date type for proper comparison. You can use the DateTime Parse Tool twice (for each column) with MM-yyyy to capture the data. Your formula should work after that.

[caltang · Accepted solution]

That is happening because you are using a matematical operator on a string, "10-2023" vs "1-2024" will probably just default to true. 

Try this:

IF ToString([MonthYear]) = ToString([Current Period])
THEN "Current Month"
ELSEIF DateTimeParse(ToString([MonthYear]),"%m-%Y") < DateTimeParse(ToString([Current Period]),"%m-%Y") 
TEHN "Accum"
ELSE "Pending"
ENDIF

Hope this helps. 

[Seba]

Thanks so much!