The Date Offset Concept

[#1]

 The Date Offset Concept is the key to ensuring that all List View views always present up to date data. It works by setting a special date filter that can be set for any date field that exists on any List View based on any data source.

This special date filter allows a time window to be defined that is pivoted around and offset from the current system date. This special date filter is called a “Date Offset filter.”

Using Date Offset filters, it is easy to build sophisticated analytics screens. For example, here is a view in the General Overview screen set up to display the last four quarters of sales by month and of the top performing BP Group Sales. At whatever date this screen is opened it will always display the updated last four quarters of data:

This example can be applied to many other scenarios, such as document or inventory aging, etc., and is just one example of the sheer power of the Date Offset functionality.

To set up simple Date Offset formula, you can drill down to the choose from list screen from the filter field of any List View date field. E.g.:

You can then drill down to an additional choose from list to select a simple Date Offset formula, e.g.:

In the above example the “Last X years” option was selected as the Time Period, after which the number 5 was entered. This generated the simple Date Offset formula “-5 Year … Today”, which means: “set a date filter from 5 years ago from today, until today.” In the brackets the system automatically displays the from and to dates that the Date Offset formula calculates when setting the formula – and these dates are subsequently updated every time the screen is newly opened, or the Date Offset filter is set again.

In addition, the Date Offset formula can be manually typed in, on either the choose from list screen or directly into the date filter field in the header of the List View itself. It is important to note that the simple approach for setting Date Offset formulae is very limited, and you can type in far more sophisticated formulae if you type them in manually.

Here is an example:
- 1 Year:Begin - 1 Day + Sun ... Year:End -1 Day + Sat

In this example both a from date and a to date are set (this does not have to always be the case). Our objective is to set a Date Offset filter that will display 2 years of transactions going from the beginning of last year, until the end of this year. However, in the specific company being worked with, there is a special rule that the financial years (which are almost the same as the calendar years) always start on a Sunday and therefore also always finish on a Saturday. So, the Date Offset formula should set the from date to be the first Sunday at the beginning of last year.

From Date calculation:

• We start from today – let’s say today is 18 May 2022.
• Then we process the first clause in the from date expression:
  • “- 1 Year:Begin”
  • This takes the from date back from today to the beginning of last year, i.e., to 1 January 2021.
• Then we process the second clause:
  • “- 1 Day”
  • This takes 1 January 2021 and subtracts 1 day from it arriving at 31 December 2020.
• Then we process the third clause:
  • “+ Sun”
  • This takes 31 December 2020 and moves forward to the next Sunday, which is 3 January 2021, which is our final from date.

The from and to dates are then separated by the ellipses “…”.

Moving on now to set the to date. We want the Date Offset formula to set the to date to be the Saturday before the first Sunday of next year. There may be several ways of calculating every Date Offset formula. Here is one way of calculating the to date:

• We start from today – 18 May 2022.
• Then we process the first clause in the to date expression:
  • “Year:End”
  • This takes the to date forward from today to the last day of the current year, i.e., to 31 December 2022.
• Then we process the second clause:
  • “- 1 Day”
  • This takes 31 December 2022 and subtracts 1 day from it arriving at 30 December 2020.
  • N.B. the reason why we do this is because it is possible that the first day of the new year can fall on a Sunday, so we must allow for the possibility when calculating the third clause below, that the last day of the calendar year might fall on a Saturday (which in this example happens to be true).
• Then we process the third clause:
  • “+ Sat”
  • This takes 30 December 2022 and moves forward to the next Saturday, which happens to fall on the last day of 2022, 31 December 2022, which is our final to date.

Hopefully, this post should give you a feel for the real power and flexibility of the Date Offset filter functionality.

Please note:

• Three new Date Offset formula expressions were added to the Date Offset filter functionality in the SAP Business One 10 FP 2111 release – see the details at the end of the “General” section here.
• Further details about Date Offset filtering can be found in our Help Portal here.