Filter Dataset with only Work Days

Question

I would like to get the days from Monday to Friday (except holidays) in my SAQL queries. For now I only have Opportunity dataset with Amount and all rows related to CloseDate (Date, Year, Month, Day, epoch, ...).

q = load "Test";
q = group q by ('CloseDate_Year', 'CloseDate_Month');
q = foreach q generate 'CloseDate_Year' as 'CloseDate_Year', 'CloseDate_Month' as 'CloseDate_Month',
count(q) as 'Nb_CloseDate',
sum('Amount') as 'All_Amount',
sum('Amount')/count(q) as 'AMT_per_CloseDate';
q = order q by ('CloseDate_Year' asc, 'CloseDate_Month' asc);
q = limit q 2000;

This code allow me to print the Sum of Amount per CloseDate in the Month. But it's not exactly what I'm looking for. All I need right now is detecting the work days in my month.

We can suppose that I have an another dataset with all date in the year with a Boolean at True if it's a work day or False if it's an Holiday (or week-end). I would like to join bot datasets with something like that :

q = load "Opp";
t = load "Work_Days";
a = q/t

In conclusion I want to know if I can solve this problem with only Opportunity dataset and how. If I can't do that how should I use the external dataset to get all work days in my month ?

Answer 1

I'll point you to these docs for a few date-related functions: https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_dayinweek.htm?search_text=daysbetween

The day_in_week() function is primarily what you would need to accomplish this:

q = load ...
q = foreach q generate 
  day_in_week(toDate(CloseDate, "yyyy-MM-dd")) as 'ClosedDayOfWeek'
  , ...rest;
q = filter q by ClosedDayOfWeek not in [1,7];
q = foreach q generate ...rest;
...etc...

SAQL is pretty restrictive so the Holiday question is a little bit harder. Holidays that are always on the same day of the year are rather straight forward. You just need to have the day/month combination as a string and do something like:

q = foreach q generate 
  'CloseDate_Day' + "/" + 'CloseDate_Month' as 'Close DayMonth'
  , ...rest;
q = filter q by 'Close DayMonth' not in ["12/25", "01/01", "01/21", "02/18". ...other holidays...];

Some other Holidays have rules for when they happen, like Easter. For those, you'd either have to create a dataset with those dates and use those as a filter, or try to account for the rules using the above logic.