4 Replies Latest reply: Nov 17, 2015 2:49 PM by Andii Toole RSS

    Using a Null Field in an Expression

      HI everyone, I'm hoping you can help, I have data that look likes below:

       

      OrderIDOrderDateShipDate
      11/1/20153/5/2015
      21/30/2015
      32/5/20153/10/2015
      46/5/20156/10/2015
      56/7/20157/5/2015
      67/1/2015

       

       

      What I am trying to do, is looking at a specific month, I want to see all orders ordered before that month, that could have shipped the current month but didn't.

       

      For Example:

      When looking at February - I have one order that was ordered before that month, but didn't ship the month it was ordered (which is a separate table in my App) and it wasn't shipped in February. Because it is a null field, if I select a ShipMonth of February it would go away, and if I select an OrderMonth of February, it would go away. However I DON'T want the order in July to appear in this selection, because it didn't have the potential to ship in February.

       

      So if I selected August. both the order in February and the Order in July should show up.

       

      How do I get this to work?

       

      Right now I have:

      =IF(IsNull(ShipDate) and (Month(OrderDate)<GetCurrentSelections(ShipMonth)), OrderDate)

       

      If I eliminate the third part of the IF Statement, and keep it to just:

      =IF(IsNull(ShipDate), OrderDate)

       

      I get every order that hasn't shipped, but I really need to make the Month selection and look at everything that was ordered prior to the month I select.