0

Multiple criteria

Anders

March 28, 2019 13:51

None

Follow

I have a report where i would like all "active" purchasing prices to show. I am therefore sorting on start date that has to be lower or equal to todays date (entered in B2) and an end date higher or equal to todays date:

=NL("Rows";"Purchasing price";;"Start date";"<="&$B$2;"End date";">="&$B$2;"+Item no.";"*";"+Supplier no.";"*")

 

I am however not getting the purchasing prices i would like. I do have some date where the start or end date is blank and i would like this incorporated in the criteria. Can anybody assist in how i add this to the existing criterias?

 

Above formula is translated from Danish to English so dont worry if the names arent exactly correct.

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis March 28, 2019 16:46

  Hello Anders.

  First, I would use the NP(Datefilter) function to build your date ranges.  

  Something like this:

  =NL("Rows";"Purchasing price";;"Start date";NP("Datefilter",,$B$2);"End date";NP("Datefilter",$B$2);"+Item no.";"*";"+Supplier no.";"*")

  You state that "I am however not getting the purchasing prices i would like".  Could you be more specific?  In what way do your results differ from your expectations?

  Next, you state that you would like to incorporate blanks in to your criteria.  In what way?  Include them in the results?  Exclude them?

  Thanks.

• 0
  
  Anders March 28, 2019 17:13

  Hi Harry,
  
  My problem is i have purchasing prices with blank start date and/or blank end date in the purchasing price table from NAV. I would like to show these as well, so my criterias need to be somewhat like:

  Start date:

  - Smaller or equal to todays date

  - Or "blank"

   

  End:

  - Bigger or equal to todays date

  - Or "blank"

  
  I do however not know how to add the blank dates as well?

• 1
  
  Harry Lewis March 29, 2019 16:26

  Hello Anders.

  Perhaps something like this:

  =NL("Rows";"Purchasing price";;"Start date";"<="&$B$2&"|@@";"End date";">="&$B$2&"|@@";"+Item no.";"*"";"+Supplier no.";"*")