DaysBetween() and RelativeDate() functions

As a follow-up to the other recent posts on variables I thought we could quickly walk-through two very useful functions for working with dates: DaysBetween() and RelativeDate(). Both are relatively easy to understand.

DaysBetween is used to calculate how many days there are between two dates. In the example below it is used to calculate the # of days between the Ship Date and the Arrival Date. Typically the earliest date is placed first within the function.

DaysBetween.jpg

The number of days are provided in an integer, as you can see in the Days Between Ex column below. There is also a MonthsBetween() function available in Web Intelligence if you'd rather get the number of months between two dates.

DaysBetween_data.jpg

The RelativeDate() function is similar except rather than providing two dates you enter one date and a positive or negative constant for how many days you want to add or subtract. For example, below we used RelativeDate() to subtract 28 days from today's date (utilizing the CurrentDate() function to get today's date). That provides the date four weeks ago.

RelativeDate.jpg

The data is as follows in the 4 Weeks Ago column. It is the same for every row in this case because the formula is using the current date and subtracting 28 days. How is this useful? Well, in this case we can now leverage another formula to create a flag. The "Used in the Last 4 Wks" column compares the Last Used Dt to the 4 Weeks Ago date and flags those where the Last Used Dt >= 4 Weeks Ago.

RelativeDate_data.jpg

Here is the logic for Used in the Last 4 Wks.

Filter.jpg

That's it for now! More variables to come...

#webintelligence #daysbetween #relativedate #variables #datefunctionsthe

Featured Posts
Recent Posts
Archive
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square
Get in touch // Business Objects Training // Tel: 763-786-5097 // info@hillyerconsulting.com