Networkdays() excluding bank holidays - suspect QS behaviour

Question & Answer
, , , , ,
1

Hi,
i’m trying to exclude bank holidays from netWorkDays calculation. Additionally i want to exclude 1 day for each record. I’m utilizing below formula and it’s partially working. However I’ve spotted some scenarios for instance: if {date1} = bank holiday, then bank holiday isn’t excluded from calculation. Is there anything I can improve within my formula? I’m utilizing Postgre SQL as a datasource.

netWorkDays({date1},{date1})

-

    (

            ifelse({date1} <= '2023-01-02' AND '2023-01-02' <= {date1}, 1, 0) +
            ifelse({date1} <= '2023-01-06' AND '2023-01-06' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-01-16' AND '2023-01-16' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-02-20' AND '2023-02-20' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-04-10' AND '2023-04-10' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-05-01' AND '2023-05-01' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-05-03' AND '2023-05-03' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-05-29' AND '2023-05-29' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-06-08' AND '2023-06-08' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-06-19' AND '2023-06-19' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-07-04' AND '2023-07-04' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-08-15' AND '2023-08-15' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-09-04' AND '2023-09-04' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-11-01' AND '2023-11-01' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-11-23' AND '2023-11-23' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-11-24' AND '2023-11-24' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-12-25' AND '2023-12-25' <= {date2}, 1, 0) +
            ifelse({date1} <= '2023-12-26' AND '2023-12-26' <= {date2}, 1, 0) +


    ) -1

results:

1st record returns expected value = 1
2nd record returns suspect value = 3 (expected 2)

image

However when tested the same data, uploaded as excel file i’m getting correct values:

image

Looking forward for any hints, thanks.

@ArunSanthosh i saw your post on similar case (How to calculate work days?), would you be able to take a look? I’ve tested out your solution but ended up with the same output.

2

Hello @Rad, this is definitely an interesting issue. I am curious, how are your date field columns formatted when they are coming in through Postgres? I know when in the table view, QuickSight will automatically format date values a specific way, so I am curious what you are seeing in the edit dataset view where it will populate your column values, how those specific dates look. My thought is, if the dates include a timestamp and are set in UTC in Postgres, that maybe on one of those 2 specific dates, something is being affected to count an extra day.

If we can’t find anything out of the ordinary there, and this is only occurring for this single date section, it may be worth adding one more check in your ifelse for these dates specifically.

3

Hi @DylanM i’m seeing such date format in dataset:

image

However it’s not the issue only for this specific date (in previous post) but it’s affecting all the bankh holidays in general i.e. each time when date1 = bank holiday, bank holiday isn’t excluded from the calculation.

4

Hello @Rad, my apologies for the delayed response here. Have you considered running some kind of alteration on the {date1}, {date2}, and manually imported date value to ensure they are all set to the same format? Running something like truncDate('DD', {date1}) <= truncDate('DD', '2023-01-02') to see if it takes that into account within the function may help resolve the issue. Another option that could be worth considering is running a convert_timezone function in your SQL query on your date fields when you bring them into QuickSight and then using those in this calculation instead.

I wish I had a more clear cut solution for you to implement here, but this one is a little harder to test locally. Let me know if you have figured anything else out during your testing and I’ll do my best to guide you to a solution.

5

Hello @Rad, I just wanted to check in and see if you still need assistance on this issue since we have not heard back from you. I apologize again for the delay on my last response, and wanted to give you a little extra time to get back to me. If we don’t hear back from you in another 3 days, I will go ahead and archive this topic. Please let me know if you have any more questions and I can do my best to assist!

It may also be worth taking some time to submit a Support ticket through AWS. This would allow them to dive into the details more thoroughly with you and help guide you towards a solution. Here are the steps to open a support case. I hope this helps!

6

Hi,
Go ahead and close the ticket, thanks.

1 Like