Hi all. I'm trying to create a report to track timeliness goals. I have searched the forums extensively for six months, but none of the solutions fit this issue well enough.... or I can't figure out how to apply them correctly. If it's here and I've missed it somehow, please feel free to redirect me specifically.
I can get the simple number of days between beginning and end. I can link to the holiday table to find out if the begin or end date is a holiday.
I cannot figure out how to bounce the days in between the begin and end date against the holiday table.
My holiday table looks like this - each day has a row:
B | Business Day | 11/29/2018 |
B | Business Day | 11/30/2018 |
H | Holiday | 11/22/2018 |
H | Holiday | 11/23/2018 |
W | Weekend | 11/24/2018 |
W | Weekend | 11/25/2018 |
Our timeliness goal is to complete every work item within 2 days.
Scenario: An item comes in on the Wednesday before Thanksgiving. We're off on Thursday and Friday for the holiday, as well as Saturday and Sunday for the weekend. The work item is completed on Tuesday. So 6 days have elapsed, but this item was completed within the 2 working days and qualifies for bonus pay.
Yes, bonus pay. If I get this number wrong, then I'm jacking with people's money, so this is a critical calculation.
This is currently being accomplished in a Crystal report (that I inherited) with whilereadingrecords/beforereadingrecords functions that Tableau does not recognize.
I'm not as smart as most of you, so please be patient with me and dumb it down as much as possible so I can learn. If you need any other information, aside from actual data, please let me know.
Thanks very, very, very much.
You need to join the production table to the holidays table with a pair of inequality joins:
begin date <= holiday date
end date >= holiday date
Now you will have one record for each day from begin to end. You can count just the "B" business days.
(Edited: I had the inequalities pointing the wrong way the first time. Sorry about that.)
Dan
Become a Viz Whiz on the Forums! Support the Community and master Tableau.