9 Replies Latest reply on Aug 29, 2017 2:22 PM by Ginny Zuckero

    The power of NETWORKDAYS with Tableau

    Dan Huff

      Hello all--

       

      Disclaimers first:

      • This is a V1 solution
      • I have done my best to check its accuracy but I did not hand verify every holiday date

       

      With that done, I want to give you all something that I spent a while over a weekend making. For those of you that have wanted to ever do a NETWORKDAYS style calculation in Tableau, you would have found that these are incredibly hard and often require you to make your own date table. For those of you whose companies observer major US holidays, this table (for the most part) can be created easily with the attached.

       

      For those of you that wanted to ever do a Sales per Day style calculation and couldn't (due to the fact you cannot always ensure that you have one sale a day preventing you from doing a COUNTD(datetrunc('day',[Order Date])) calc), the attached may also allow you to address this problem.

       

      The underlying issue with both of these calculations is that you often find yourself in need of a date table that flags any given day within any given year as a business day, a weekend, or an observed holiday on which your business is closed. This can be tricky and time consuming. In the excel document attached, this is now quite easy to do. In order to create your own date tables, please simply follow the instructions on the Read Me tab of the excel document. Once you have this done, you can use the attached workbook to see examples of how I have used the date tables to create both Sales per Day calculations and Average Networkdays to Ship calculations. The Networkdays + Superstore connection is accomplished by joining and extracting the Networkdays table in the attached excel doc into the Sample Superstore dataset that shipped with Desktop V7. You can see the join criteria by editing the connection. Additionally, there is extra information in the captions on some of the sheets to explain why I did the calculations in the manner that is presented. The workbook was created in Dekstop V7 so it will open in both 7 and 8.

       

      Regarding the excel document, here is a general caveat list:

       

      • My holiday calculations DO NOT adjust holidays to the previous Friday or following Monday if a holiday falls on a weekend
        • You can manually adjust  this by finding the holidays on the Holiday List tab though
      • I took a bit of a liberty with MLK day. Though established in 1986, it was not officially celebrated by all states until 2000. I chose to ignore this and added all instances between 1986 and 2050
      • I include the 24th, 25th, and 26th of December as these are commonly offered off by U.S. based companies. This is by no means me ignoring other religions' holidays.
      • Again, I have done my best to ensure the correctness of all the dates for the holidays which are variable. I am not aware of any errors but I’m not 100% confident this is error free.

       

      Please feel free to comment on the attached as to its usefulness and/or accurateness (sp?). Also, feel free to critique this if you think it is rubbish .

       

      Dan

       


        • 1. Re: The power of NETWORKDAYS with Tableau
          Dustin Smith

          Uh oh, another Tableau Dev makin' waves in the forum.

           

          Pinging Tracy Rodgers James Baker Richard Leeke Joe Mako Jonathan Drummey - You think this belong in TCRL?

          • 2. Re: The power of NETWORKDAYS with Tableau
            Jonathan Drummey

            This is great!!

             

            I'm all for this going into the TCRL, I've got some questions that I think should be addressed first. My main comment is that Excel spreadsheet could be better arranged:

             

            - put the worksheets that the user edits first (rather than last)

            - mark all cells that the user is supposed to edit with a different background color, that way they know what to pay attention to.

            - I'm not clear on what is meant by "change the dates on the holiday list tab". Does that mean after the holiday list is generated a user would do manual updates?

            - I never understood the logic about bank holidays in the UK, it would be good to get input from Craig Bloodworth or Tom Brown. Also, Alex Kerin would be another good person to ask for input as well.

             

            Secondarily, I think whatever documentation could be more clear about the level of knowledge of Tableau necessary to pull this off. In other words, who is the audience for this? Here are places I can see where this gets a little advanced:

             

            - The workbook uses blending

            - The description says the NetworkDays + Superstore data source uses a join, but an extract is used so I don't know what kind of join, I'm guessing a left-join??

            - There are table calcs involved (that I'm not sure are completely necessary, it seems like a row level calc without Row ID in the view would work).

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: The power of NETWORKDAYS with Tableau
              Mark Moran

              Dan,

               

              I have a twist on this.  I am trying to calculate the number of trouble tickets resolved in 2 business days or less.  I can't wrap my head around it.  I need a COUNTIF function or something like that.  Any suggestions you can offer is much appreciated.

               

              Mark

              • 4. Re: The power of NETWORKDAYS with Tableau
                Alex Kerin

                Mark, that will depend on the structure of your data. If open and close are in the same column on different rows, then this may help: http://www.datadrivenconsulting.com/2012/10/queues-and-utilization-in-tableau-part-1-queues/

                1 of 1 people found this helpful
                • 5. Re: The power of NETWORKDAYS with Tableau
                  Mark Moran

                  Alex, thanks for the link.  It sparked some ideas and I figured it out albeit in a crude manner.

                   

                  I created a measure where if the Network Days are less than 3 (i.e. in 2 days or less) then assign a value of 1, if not 0.

                  Then I did a simple sum/count calculation on the new measure.

                   

                  Mark

                  • 6. Re: The power of NETWORKDAYS with Tableau
                    scott.englander

                    Thanks for this. There's an error on the Holiday Tab -- Veterans Day is misspelled in R424:R494 (as "Veteran's Day"), resulting in erroneous data being returned from the lookup. This could have been detected by setting the range_lookup parameter in the vlookup formula as FALSE, e.g.,

                     

                    =VLOOKUP(R424,$J$14:$O$26,6, FALSE)

                    • 7. Re: The power of NETWORKDAYS with Tableau
                      Mark Rosendale

                      I think this is great.  I used this on one file based on monthly data and it worked perfectly..  I am trying to set it up to use on a daily and month to data file.  The formula I use works for each row, but does not work for the total.  Can anyone help with the calculation so the Grand Total is correct?

                      Here are the calculations.

                      Business days

                      Capture 1.PNG

                      Revenue per driver per business day

                      Capture 2.PNG

                      I know I get the right answer for each line, but not the total.   Revenue per Driver per Day (Copy 2) is using the above calculations.  Business days Calc is right for every Driver Division.  The  Grand Total is right for the Revenue per Driver per Day, but not for the copy.  The Revenue per Driver per day uses a business day calculation uses DATEDIFF and DATEPART but does not work for holidays.

                      Capture 3.PNG

                      • 8. Re: The power of NETWORKDAYS with Tableau
                        Liat Ferman

                        Hi

                         

                        First of all - I loved it!

                        Just one more crazy thought - if I were to add \ calculate in Tableau the net work hours , any idea how can it be done?

                        • 9. Re: The power of NETWORKDAYS with Tableau
                          Ginny Zuckero

                          My issue is that my data has a date/time field that I need to match against this to define the work day. Tableau won't let me change the date so I can't join and therefore still can't get Net Work Days.

                           

                          Excel has this built in, why doesn't Tableau?