How to calculate work days?

Question & Answer
1

Customer question - How can I calculate work days in QuickSight? (ie - exclude weekends and other specific holidays)

2

You can do work day calculations using a combination of existing calcs.
I have posted a sample at https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Work-Days

Calculation used is

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({Start Date},{End Date},'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',{Start Date}),truncDate('WK',{End Date}),'WK')*5
        //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',{Start Date}) = 1, 0, extract('WD',{Start Date})-2) 
        //Along same lines, we got to add the weekdays from Sunday prior to end date 
        + ifelse(extract('WD',{End Date}) = 1, 0, extract('WD',{End Date})-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)
        //)
    )
)
3 Likes
3

Can this be done by using the isworkday calculation now? isWorkDay - Amazon QuickSight

4

@ArunSanthosh Can this be made dynamic based on the list of Holidays for a given year?

5

Hi Harshit @sidhwh ,

netWorkDays function can be used instead of custom logic now.

Hi Aishwar @Aishwar,

The custom calc includes option to account for holidays other than weekends. Just uncomment the last block and add in the holiday dates there. It is a static list though. We do have a PFR in queue for enhancement to netWorkDays function to use custom holiday list. Please have your AWS Technical account manager / Solution architect submit a customer influence on your behalf if this is of interest. (Internal PFR link). In case you don’t have an AWS contact, send me your org & use case details and I will add the customer influence for you.

image
image1646×604 91.3 KB

Regards,
Arun Santhosh

6

What about, getting the working hours?