Alert Monitoring
Hello Everyone,
I've setup a weekly scenario tool that shows your starting and ending weekly inventory with the line items being the key factors like Forecast, Sales Orders, Purchase Orders, Planned Orders, etc... I export this data from SAP. I'm basically trying to recreate the planning book because our company doesn't really need it.
However, I have been told to recreate the Alert Monitoring functions in SAP. One of the basic calculations I've done is Weeks of Inventory On Hand. Anytime it gets below 1 weeks I have a Boolean. What I want to do is have a formula that essentially says if TRUE display the week starting date. The problem is IF statements can't display dates as an output.
This isn't an issue in excel so I'm confused as to why it is here.
Best Answer
-
here's a mock up for you. My calendar is in months so I added the number of weeks per month.
If you use weeks you won't need to do that.
Results:
Blueprint
0
Answers
-
You might make sure that the line item is formatted as Boolean if you want the result to be TRUE or FALSE.
The formula will look something like this (remember, any time you use an IF statement you want to try to exit as soon as possible). Alternatively, you can try to use AND/OR with the same mentality.
IF [WEEKS OF SUPPLY] > 1 THEN FALSE else TRUE
Where WEEKS OF SUPPLY is your calculation.
If you want the result to be a date, then format the line item as date or period depending on what you want.
IF [WEEKS OF SUPPLY] > 1 THEN BLANK else [Date or Period]
0 -
Yeah like I said IF functions don't accept dates as outputs
Datatypes do not match for 'IF' function: DATE, NUMBER
0 -
You are my hero, I guess adding FALSE was what I was missing
1