SAQL: Calculating Day Difference Minus Weekends

SAQL (Salesforce Analytics Query Language) is a powerful tool used for data analysis and reporting within the Salesforce platform. Sometimes, you will need to use SAQL to compute something that is more complicated that you can do via the UI. This post will explain calculating day difference minus weekends using SAQL. One useful function in SAQL is date_diff, which allows users to calculate the difference between two dates in a straightforward manner.

By specifying the desired date units, such as days, weeks, months, or years, SAQL’s date_diff function can efficiently determine the time span between two dates.

Syntax

date_diff(datepart,startdate,enddate)

An example coming from the SAQL Developer Guide looks like:

q = load "DTC_Opportunity";
q = foreach q generate date_diff("week", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch) ) as 'Weeks to Close';
q = order q by 'Weeks to Close';

How about excluding weekends?

There is no parameters or options we can include in the date_diff function that gives us the ability to exclude weekends.

The following piece of SAQL will calculate the day difference minus the weekends. This is using the Opportunity object as an example.

q = foreach q generate CreatedDate, CloseDate, date_diff ("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) as DayDiff, 
floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7)*2 as Weekends, 
date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) -floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7)*2 as adjDiffDays ;

Each derived field above builds off the previous formula. The piece of code above can exist in a lens or a recipe.

Using Dates with SAQL Examples

Check out salesforceblogger.com for more examples.