1 Reply Latest reply on Dec 21, 2015 12:33 PM by Norbert Maijoor

    Calculating Prior Business Day

    Joan Pecht

      Happy Holidays!


      I have quite the brain stumper (for me) and am hoping someone can provide some insight.


      I have a very simple dataset (attached) with dates associated to hours and employee worked. I want to be able to select a date and have it show that date AND the prior business date. Meaning if I select 11/24/15 it will show me data for 11/23 and 11/24. Holidays and weekends being excluded.


      Initially I created a Select Date Parameter and I uploaded a holiday table that includes EVERY date in for the next 70 years and 1) whether it is a weekday, Saturday/Sunday, or Holiday. I even made it simple and added a column that says # of Days to Prior Business day. Meaning 11/24 (Tuesday) shows 1 so I could do a DateAdd('weekday',-1,[Date]) or for 11/27 (Friday after Thanksgiving) shows 2 so I could do a DateAdd('weekday',-2,[Date]).


      My problem is that I can't figure out a way to link this holiday table to a parameter to perform the calculation. My other solution is to write out the logic that would indicate #Days to go back but that gets trick when the holiday falls on a Friday (because then your following Saturday, Sunday, and Monday calculations have to be different). My last solution is to simply have 2 date parameters and have the user select the two dates. I'm hoping to avoid this but I just can't think of anything else. Is there a business day calculator in Tableau? I couldn't find any reference to one. Any other options that I'm missing?