Last week To Date Behaviour on Saved Search

Scenario

1. Today’s Date: September 18, 2021
2. Navigate to Transactions > Sales > Enter Sales Order > Search or any similar searches
3. Date: Select Last Month to Date or Last Week to Date or Last Year to Date

Expected Behavior

Users might think that the date populated in the From and To fields would be:

• Last Month to Date
  • From: August 1, 2021
  • To: September 18, 2021
    
• Last Week to Date
  • From: September 6, 2021
  • To: September 18, 2021
    
• Last Year to Date
  • From: January 1, 2020
  • To: September 18, 2021

Actual / Netsuite Standard Behavior

The Last Month to Date, Last Week to Date, or Last Year to Date is usually used for comparison.

• Last Month to Date
  • It means you are comparing the sales at the start of the date last month up to the date similar to today’s date in the last month. The date populated in the From and To fields will be following:
  • From: August 1, 2021
  • To: August 18, 2021
    
• Last Week to Date
  • If you use Last Week to Date, its like saying last week thru the same day of the week as today. Since today is Saturday, it will show the sales start of last week to Saturday last week.
  • From: September 6, 2021
  • To: September 11, 2021
    
• Last Year to Date
  • From: January 1, 2020
  • To: September 18, 2020

Solution

If we need to get the Data from the Previous period to Date, use some Formula with custom Date criteria

Example for Last week to Date(Today)

• Select Formula (Numeric)
  1. Formula: Enter
     CASE WHEN {createddate} BETWEEN {today}-(to_number(to_char({today}, ‘D’))+7) AND {today} THEN 1 ELSE 0 END
  2. Formula (Numeric): Select Equal to
  3. Value: Enter 1