- 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 .