12 Replies Latest reply on Jun 12, 2015 1:30 PM by Tom W

    Holiday calculation excluding Holidays and Weekends by Region

    Naquiyah Cash

      Hi All

       

      I have read numerous chats on this but wanted some help before I decide to jump off a bridge.

       

      Didn't realize how hard this would be. Wish tableau had a way for us to just insert working hours, weekends and holidays automatically.

       

      I have to calculate working hours for employees  excluding weekends and holidays by city/country WW urghh I know.

       

      I am literally given an excel sheet with the days of the holiday on tabs split by city. Is there anyone who can help or any links I haven't yet found to help with the best way to input this data for this year?

       

      Shall I create something in excel and upload and if so does anyone know of any templates out there to make this a little more doable to not drive me insane?

       

      I'm thinking of just creating something in excel and then adding that as a data source for working hours?

       

      If you need anything from me I am more than happy to provide it.

       

      Thanks a lot guys

       

      Naquiyah

        • 1. Re: Holiday calculation excluding Holidays and Weekends by Region
          Tom W

          It depends entirely on the structure of your data. You should start by uploading a Tableau Packaged Workbook with that data.

          • 2. Re: Holiday calculation excluding Holidays and Weekends by Region
            Sai Manikanta N

            Hi Naquiyah,

             

            We can exclude week end by using Date Part

            >Get WeekDay by DATEPART('weekday',[Date]) will give 1-7 (Sunday(1), Monday(2) and so on)

            > Create cal fiels to exclude 1 and 7 (Sunday and Saturated) and keep the field in filter show on WeekDays

             

            -

            Sai

            • 3. Re: Holiday calculation excluding Holidays and Weekends by Region
              Naquiyah Cash

              Tom W

               

              Hey Tom

               

              So I am given our company holidays in a word doc. Yes I know urgh. Anyway I could excel this but before I do wanted to figure out the best way to do this.

               

              Attached I have the holiday schedule worldwide for this year. I then have a workbook showing employees that have tracked time and those that have not WW.

               

              This is what I need to show. A graph similar to the below that shows time tracked by employees by dept on a bar chart. Then a utilization % over those bars and the calculation for that is

               

              Utilization Rate % - Total time spent MTD / Total hours MTD for all employees (include employees who are also not tracking time)

               

              1. The Total Hours MTD is basically all hours for the number of employees we have excluding weekends and holidays.
              2. Once we get that figure and mapped to the city we can create the visualization.

               

              Note: To get the Total Hours MTD we have to take into consideration the employees live in different cities and hence have different hours they work on.

               

              Let me know if you want me to somehow put the word doc in excel? and if so whats the best format or way you recommend?

               

              Working hours are a normal 7 hours a day.

               

               

              Screen Shot 2015-06-10 at 5.35.30 PM.png

              • 4. Re: Holiday calculation excluding Holidays and Weekends by Region
                Tom W

                I don't understand where holidays come into it - if you have total time tracked, why do you care if there was a holiday?

                That's not clear in your example.

                • 5. Re: Holiday calculation excluding Holidays and Weekends by Region
                  Naquiyah Cash

                  Basically we are measuring those people that have tracked time and those that have not and should be tracking time. However to get the should be tracking time part we need to calculate the "Working Hours" for all employees. The working hours would therefore exclude holidays and weekend for where the employees are based to get that calculation.

                   

                  Apologise for the confusion.

                   

                  I can calculate everything else just need to figure out the best way to calculate working hours (excluding holidays and weekend) for all employees. Caveat is the holidays are not the same as they are in different cities and I get the holidays in a silly word format.

                   

                  Was wondering if there was a template on excel or any other ideas anyone had to help make this an easier process without losing any hair

                   

                  thanks

                   

                  Naquiyah

                  • 6. Re: Holiday calculation excluding Holidays and Weekends by Region
                    Tom W

                    You'll need to provide a sample of the data in it's original format that we will be basing the calculations off rather than the output.

                    How do you current store the hours worked by an employee? There are a bunch of different ways it could be stored and each of them will require different calculation methods. I.e. is it a start and end date per shift, a start date with a duration? etc

                    • 7. Re: Holiday calculation excluding Holidays and Weekends by Region
                      Naquiyah Cash

                      Hi Tom

                       

                      We have a time tracking tool thats used by the employees in SFDC. So I have two connections. One is the Time tracking data pulled from SFDC. In that we can see the employee and the time (Hrs) they tracked and on what day. We don't really care exactly what time during the day as long as we get the day that they did track.

                       

                      The other file is the user details of all our employees. With the two files I have

                       

                      • One which is employees who tracked the time and then their details in the other file.
                      • The other file has all the employees so I can see those who did not track time and should have.

                       

                      I have also added a date mapping table if this helps with the calculations?

                       

                      So I am trying to figure out with the data we have how to get working hours.

                       

                      Working hours MTD

                       

                      • Take all employees weather they tracked time or not
                      • Exclude holidays from the sheet HR provided (taking into consideration the city the employee is in)
                      • Exclude Weekends (Sat, Sun)
                      • Working Hours 7hrs per day
                      • Calculation for this is MTD.

                       

                      I have attached the twbx and the raw excel sheets if needed. Let me know if this helps. The data in there is a mockup of the real thing. instead of Employee name we have employee ID

                       

                      Hope this helps. Also thanks a LOT Tom for helping me on this!!! Anything else you need me to do please let me know.

                      • 8. Re: Holiday calculation excluding Holidays and Weekends by Region
                        Tom W

                        What's your data source/s? Will you be connecting to excel sheets or database?

                        • 9. Re: Holiday calculation excluding Holidays and Weekends by Region
                          Naquiyah Cash

                          MySQL databse

                           

                          We have an ETL from SFDC to MySQL database that refreshes everyday.

                          • 10. Re: Holiday calculation excluding Holidays and Weekends by Region
                            Tom W

                            Here's what I'd suggest;

                            > Combine your TimeEntry and User Details into one query in mysql.

                            > Create a file in Excel to track your holidays by using the City, State, County as the key and then include the date if there's a holiday.

                             

                            See my attached example. I've mocked up one connection which would be your time entry + user details combined and I've connected it to my City Holidays file using blending (a left join from the time entry to the holiday file).

                             

                            I've created two calcs for HolidayHours and Regular hours. You can see the formula within is basically saying if the date in the holiday file is null, no corresponding record could be found in the holiday file so it must be a regular working day while the holidayhours field is the opposite.

                             

                            Some things to keep an eye out for:

                            > You'll need to make your join on more than city, I'd suggest City, State and Country to keep it unique.

                            > Your data isn't consistent. CA and California are the same thing. You either need to scrub that data so you only have CA or you'll have to have double entries in your holiday file to account for CA and California.

                             

                            This isn't going to get you the whole way to your solve, but it does demonstrate how it's possible to get it started using blending. Then you can work your own calculations into it and build on it.

                            • 11. Re: Holiday calculation excluding Holidays and Weekends by Region
                              Naquiyah Cash

                              Hey Tom

                               

                              Thanks for that. I like your idea of the unique ID for holidays. I took that onboard and created a file for that. The unique ID will be the Office with a relationship with the user file on Office.

                               

                              Screen Shot 2015-06-12 at 3.10.31 PM.png

                               

                              So I may have confused you a little but here is some clarifications and hoping you can help with a formula creation.

                               

                              • The join between user sheet and time entry users won't work because I need all the users sheet to be there.

                               

                              Below is a bar and line chart -

                               

                              1. Bar chart: The bar chart is all users that are tracking time. So that will be the Time Entry sheet. This is easy and already in the workbook.
                              2. Line chart: This is the utilization rate which is = Time tracked on Time entry sheet/ All employees working hours.
                                1. All Employees working hours is the pain. This is taking the users sheet with all employees. Calculating a count of users and then * by working hours.
                                2. Working Hours is The weekday hours and if there is a holiday then subtract that from the hours for the user.
                                3. Weekday hours are 40 hours a week (The date file could help here as there is a week mapping.)
                                4. Or we can find a another way to exclude weekends?

                               

                              Let me know what you think on a how to calculate this. I have attached the packed workbook with all the data in. We have the Date mapping, Holidays I created, Time Entry and All user data. P.S - Ignore the blanks on office we are still updating that.

                               

                              Screen Shot 2015-06-12 at 2.56.50 PM.png

                               

                               

                              Thanks a lot for your help here. I am pretty new to tableau and calculations like these just get me lost.

                               

                              Thanks

                               

                              Naquiyah

                              • 12. Re: Holiday calculation excluding Holidays and Weekends by Region
                                Tom W

                                Your chart proposals are effectively irrelevant if you can't get the data shaped in a way which allows you to join in Tableau.

                                 

                                You will only be able to connect one data source in Tableau to your Excel file with the holiday dates. So you cannot daisy chain three together with Users > Time Entry > Excel Holidays.

                                You'll have to come up with some way to join it so it's Users + Time Entry joining to Excel Holidays. If your only requirement is that you show all users, then do a left join in your database between Users and Time Entry.