0

Report based on dates prior to TODAY

Jet Reports Historic Posts

March 08, 2017 21:28

None

Follow

I am trying to create a report of open Purchase Lines (from NAV) where the expected date is less than today's date.

I have no trouble using the =TODAY variable, but can't figure out how to get any dates PRIOR to TODAY.

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts March 08, 2017 23:26

  You can replace the =TODAY() function in cell D3 with any valid date.
  
  Then, when the report is run, any function which references that cell will use the specified date.

• 0
  
  Jet Reports Historic Posts March 09, 2017 12:44

  You can use the NP datefilter
  
  =NP("DateFilter",,NP("EVAL","=today()-1"))

• 0
  
  Jet Reports Historic Posts March 09, 2017 17:36

  Thanks for the replies.
  
  I don't want to put a fixed date in the cell, because I would like to automate the report so that every day, we'll know what purchased items are late (have not been received yet).
  
  So I think I want to do this: NP("DateFilter","01/01/06",$D$3)
  
  But I already have this: =NL("Rows","Purchase Line",,"Expected Receipt Date",$D$3,"Qty. to Receive",">0","Document Type","Order")
  
  How do I put them together?

• 0
  
  Jet Reports Historic Posts March 09, 2017 18:18

  I think I figured it out.
  
  Today's Date in Cell D2: =TODAY()
  Cell D6: =NP("DateFilter","01/01/06",$D$2)
  
  Cell F7: =NL("Rows","Purchase Line",,"Expected Receipt Date",$D$6,"Qty. to Receive",">0","Document Type","Order")

• 0
  
  Jet Reports Historic Posts March 15, 2017 14:50

  You could also have done
  
  =NL("Rows","Purchase Line",,"Expected Receipt Date","<="&$D$2,"Qty. to Receive",">0","Document Type","Order")
  
  or
  
  =NL("Rows","Purchase Line",,"Expected Receipt Date",".."&$D$2,"Qty. to Receive",">0","Document Type","Order")
  
  where $D$2 is =TODAY().