1

Find Maximum Number of Days Between Two Dates

Simon Bellinger

June 21, 2022 16:29

None

Follow

Hi all,

I'm looking to build a report, based on the NAV 'Cust. Ledger Entry' table, to summarise how well customers adhere to their payment terms.

One of the columns I'm looking to include within the report is 'Maximum No. of Days to Pay', which should return the highest number found when calculating the difference between 'Closed at Date' and 'Document Date' for each entry within the applied filters.

I can't quite figure out how to achieve this.

The following formula successfully returns the unique 'number of payment days', so I essentially just want the maximum value from these rows (but without actually returning the rows and then calculating the max separately).

=@NL("Rows","Cust. Ledger Entry","=NF(,""Closed at Date"")-NF(,""Document Date"")","Closed at Date","<>''","Document Type","Invoice","Document Date",Options!$C$3,"Customer No.",$D$4)

I'm hoping it's one of those things where I'm quite close to the answer, but I just can't seem to get it right. Any help would be greatly appreciated.

Many thanks,
Simon

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Central 365 July 05, 2022 16:23

  Not too sure if this is a solution to your problem but :
  Have you given a try testing =NL(First) and =NL(Last) and applying a sort on the desired field ?

  
  Jet Central 365

  Your Jet Reports & Jet Analytics Partner.

  For all your Jet Reports & Jet Analytics consulting services : sales@jetcentral365.com

• 0
  
  Simon Bellinger July 06, 2022 07:35

  Hi, thanks for the response but, unless I'm understanding the suggestion wrong, I'm not sure that will work.

  I believe First and Last would allow me to calculate the number of days between the oldest and newest entries, but what I actually want to do is evaluate the number of days for each individual row in the table, and then return the highest number found (i.e. what was the longest amount of time the customer took to pay?).

  Perhaps you could elaborate on the suggestion in case I'm misunderstanding?

  Many thanks

• 0
  
  CascadiaDataworks July 07, 2022 21:18

  I believe what they are saying to replace "Rows" with "First".  When you do this, you will need to apply a filter with a sort to ensure that you are pulling either the highest or lowest value. 

  Might look something like: =@NL("First","Cust. Ledger Entry","=NF(,""Closed at Date"")-NF(,""Document Date"")","Closed at Date","<>''","Document Type","Invoice","Document Date",Options!$C$3,"Customer No.",$D$4,"-=NF(,""Closed at Date"")-NF(,""Document Date"")","*").

  That may not be perfect on the formatting so you may need to tweak that a bit.

  (Side note, always use NL("First") with a reverse sort to mimic NL("Last") with a normal sort.  NL("Last") is super inefficient as Jet will have to get all results and then go through all to find the last whereas First is essentially a "top 1") 

  Edited by CascadiaDataworks July 07, 2022 21:18