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 ?