hello
I need help to count the working days of the specific month until today.
I’m already using the script below for this calculation on the daily axis, but I need it for the monthly axis
min(
//Get the days between start and end dates.
//If this is 0 or negative, the end date is same or prior to start date. So, set the work days to 0
ifelse(dateDiff( truncDate(“MM”,{Data da venda}) , truncDate(“DD”,{Data da venda}) ,‘DD’) <= 0, 0,
//If end date is later than start date, let’s calculate further.
//Get the sunday prior to start and end dates using trunDate function,
//get the number of weeks between those Sundays using outer dateDiff and multiply by 5 to get work days therein.
dateDiff(truncDate(‘WK’, truncDate(“MM”,{Data da venda}) ),truncDate(‘WK’, truncDate(“DD”,{Data da venda}) ),‘WK’)*6
//Since we are calculated above days from Sunday prior to start date,
//we got to subtract number of working days between that Sunday and actual start date.
//ie if actual start date is on Sunday or Monday, subtract 0.
//If it is Tue, subtract 1, so an and so forth.
//When we extract weekdays, Sun is 1 and Sat is 7. So, we can get the value to subtract using below logic
- ifelse(extract(‘WD’, truncDate(“MM”,{Data da venda}) ) = 1, 0, extract(‘WD’, truncDate(“MM”,{Data da venda}) )-2)
//Along same lines, we got to add the weekdays from Sunday prior to end date
+ ifelse(extract(‘WD’, truncDate(“DD”,{Data da venda}) ) = 1, 0, extract(‘WD’, truncDate(“DD”,{Data da venda}) )-2)
//Next, account for any other holidays other than weekends by making an explicit entry below for each date.
//Uncomment below block if you want to see that changes if Dec 30th and 31st are considered holidays.
//-
//(
// ifelse({Start Date}<=‘2021-12-30’ and ‘2021-12-30’ <{End Date}, 1, 0) +
// ifelse({Start Date}<=‘2021-12-31’ and ‘2021-12-31’ <{End Date}, 1, 0)
//)
)
)+1