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

  • Hi jedge@dsi.com 

     

    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.

    AlejandroGomez_0-1579887407335.png

     

    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.

    AlejandroGomez_1-1579887506377.png

     

    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] .

    AlejandroGomez_2-1579887585803.png

     

    I hope it helps.

     

    Cheers,

     

    Alex.

     

    Olivehorse.

Answers