0

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 ?

0

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.

  • Thanks it helps a lot ! – Jack Apr 5 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.