FIRSTNONBLANK FOR ALERT MONITORING
OK, I've already gotten one question answered around this. Just to recap, I'm creating an alert monitoring system that will flag when a stock goes low stock or out of stock. I have a BOOLEAN for when a stock goes below 1 week of inventory and another line item that displays that start date of that week should we go out of stock, ditto the last day of the week.
Now, I want to pull the FIRSTNONBLANK of the start of the week and the last day and the LASTNONBLANK for the end of the week. This will give me my stock out time period. I've created a module that doesn't have a time dimension because all I want are those dates and then I can do some calculations based on those 2 dates. I've used this formula but it doesn't seem to be working
Weekly Scenario.Start of Week[FIRSTNONBLANK: Weekly Scenario.Start of Week]
and this
Weekly Scenario.End of Week[FIRSTNONBLANK: Weekly Scenario.Start of Week]
It's my understanding that the line item outside of the brackets is the test, meaning this is where we look for the first blank, and the second is what line is referenced. Any thoughts? These are my formulas for my weekly scenario tool that is driving the dates, I'm creating a separate Alert Monitor that will pull those dates out.
Best Answer
-
That is not quite the way FIRSTNONBLANK works, I believe. Here is why: the item in the brackets must be formatted as a dimension that you have in your target module. Lets take this example form Anapedia: customers is list formatted in the Source and is a dimension in the target.
So, in your case, Time is the dimension you want to get rid of, so that is why is not working. I suggest as a work around for this, to use use the Summary Methods from the source line items:
In your source module, in the Line Items calculating the Dates, goto summary methods and set time summary to First non blank or Last non blank.
Then in your target module, just refer to the Line Items with the dates that you want and use the function [SELECT: TIME.All Periods] .
I hope it helps.
Cheers,
Alex.
3
Answers
-
-
Hello Jedge,
You need to show your source and taget dimensions.
I think you must be having Product & Location dimensions apart from time. If they are present in both source and destination, then you dont have to include them inside the square brackets, but you need to keep the summary method of "Start of Week" as FIRST NON BLANK as well, as it is the natural summary method.
Thanks
Arun
0 -
That works to get us started. Thanks much!
0 -
Appreciated!
0
