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 ?