12 Replies Latest reply on Nov 13, 2015 12:52 AM by kettan

    Flag for Overlapping Time Period

    kelly.schmittel

      I need to do 2 things. I need a calculated field that will check if an employee has an overlapping Shift In and Shift out time for the day.

      EXAMPLE: Employee 10057 worked March 7th from 1pm to 4pm, and also March 7th from 3PM to 5PM = Flag as not possible

       

      I also need a calculated field that would check if more than 1 employee was at the same store number at the same time.

       

      T/F calculation would be nice so I could drop it on the color shelf or use as filter.

       

      Below is a sample table I'm working with. Calculation 1 is just checking hours worked.

       

      Any help would be greatly appreciated. Thanks.

       

        • 1. Re: Flag for Overlapping Time Period
          Steve Martin

          Hi Kelly,

           

          I just want to focus on the first part of your question to begin with, that of flagging the impossible times.

           

          Can I ask, what it is you are wanting to do with this flag? As in, are you wanting to look for those employees who are flagged or are you wanting to filter the data? on the first hit only?

           

          Steve

          • 2. Re: Flag for Overlapping Time Period
            kettan

            This thread from last Thursday might be helpful:  Identifying Overlapping Dates

            • 3. Re: Flag for Overlapping Time Period
              kelly.schmittel

              Steve, I think looking for or filtering any flagged employee would be fantastic. The point would be to easily see which employees aren't being 'truthful' with their hours, which unfortunately does happen. I didn't really follow the thread that kettan had left. I was thinking this would be a simple calculation of some kind but I guess not?

              • 4. Re: Flag for Overlapping Time Period
                kettan

                If it is enough to check previous 'row', then LOOKUP is probably all you need. Could you share the data of your screenshot? This makes it easy for helpers to play with a formula till it works.

                • 5. Re: Flag for Overlapping Time Period
                  kelly.schmittel

                  Attached is the file I'm working with. So the goal again is to identify if multiple employees are at the same store number at the same day & time, and/or If a single employee is reporting multiple shifts during the same day & overlapping time.

                   

                  Thanks for digging into this.

                  • 6. Re: Flag for Overlapping Time Period
                    Steve Martin

                    Hi Kelly,

                     

                    How do you come by this data? Is it already provided as a spreadsheet or do you generate it through sql?

                     

                    I am thinking it might just be easier to perform this at source if the option is available rather than create a more complicated solution in the presentation layer.

                     

                    In the meantime, try the attached.

                     

                    Steve

                    • 7. Re: Flag for Overlapping Time Period
                      kelly.schmittel

                      It’s a file that is auto generated from an online employee system. I did find a good tableau example at this link:

                       

                      https://www.tableau.com/about/blog/2015/10/are-you-tableau-smart-tableau-consultant-45195?hootPostID=bf156bbbb5c535e47b95e655bc6b65cf

                       

                      Under Jedi: Overlapping Time Frames. The issue I can’t figure out is I’m dealing at the hour level, and this example is by day so I’m not sure how to handle the difference.

                       

                       

                      • 8. Re: Flag for Overlapping Time Period
                        Steve Martin

                        Can I assume my attached did not help then?

                         

                        Steve

                        • 9. Re: Flag for Overlapping Time Period
                          kettan

                          the goal again is to identify if multiple employees are at the same store number at the same day & time, and/or If a single employee is reporting multiple shifts during the same day & overlapping time.

                          I tried to find a solution inside Tableau (with table calculations etc) but didn't come to anything worthy to be shared.

                           

                          It is of course easier for helpers to play their way to a solution when they know what expected result is. Could you confirm if attached is expected result for a single employee is reporting multiple shifts during the same day & overlapping time?

                           

                          This is the custom SQL used:

                           

                          select t1.[Employee Id], t1.[Store Number], t1.[ShiftIn], t1.[ShiftOut]
                               , t2.[Employee Id], t2.[Store Number], t2.[ShiftIn], t2.[ShiftOut]
                          from [Sheet1$] t1, [Sheet1$] t2
                          where t1.[Employee Id] = t2.[Employee Id]
                          and   t1.[ShiftIn]     < t2.[ShiftOut]
                          and   t1.[ShiftOut]    > t2.[ShiftIn]
                          and not (
                            t1.[Store Number] = t2.[Store Number] and
                            t1.[ShiftIn]      = t2.[ShiftIn]      and
                            t1.[ShiftOut]     = t2.[ShiftOut]
                            )
                          
                          
                          
                          
                          
                          

                           

                          Line 4 is a normal self "inner join" on [Employee Id].

                          Line 5 and 6 checks if shift overlaps other shifts for the same [Employee Id].

                          Lina 7 to 11 excludes duplicate registrations assuming they don't count as overlapping periods

                           

                          thread 193564 Flag for Overlapping Time Period - Q1 Employee Id.png

                           

                          I saved attached workbook's data source as extract to improve performance (response time).

                           

                          Please notice that you need to open Excel with the legacy connector to be allowed to use SQL (Jet):

                           

                          Open with Legacy Connection 2.png

                           

                           

                          Attached Workbook Version:  9.0.

                          • 10. Re: Flag for Overlapping Time Period
                            kelly.schmittel

                            Yes, That is the result I am looking for. I seem to be having trouble recreating it. Can you send a pic of the data connection joins. Below is what I have set up with the result I get. I think I'm close, but I don't quite get the results you did. I'm getting 2 rows for the same occurrence See first employee ID.

                             

                            Lastly, How do we switch this up so that the result could be if more than 1 employee is at the same store during the same time? Is that just a switch of Employee ID and Store number in below join?

                             

                            • 11. Re: Flag for Overlapping Time Period
                              kettan

                              Great idea to use the new connector rather than the legacy connector!  

                               

                              I just tried to do same but didn't get the option to choose < and > like you 

                              This might be because I am using 9.0 (expired license)

                               

                              I am confident you get the same result as I if you remove the last 3 join conditions and add them as a single calculated T/F filter. The below screenshot is with the legacy connector:

                               

                              thread 193564 Flag for Overlapping Time Period - Duplicate TF.png

                              • 12. Re: Flag for Overlapping Time Period
                                kettan

                                Solution shared was made with Tableau 9.0 and the Legacy Connector. See more in attached workbook.

                                You  showed  that the New Connector in Tableau 9.1 can do most of it! and thus likely the one you use.

                                 

                                As for using the Legacy Connector, we need to open Excel with the option 'Open with Legacy Connection':

                                Open with Legacy Connection 2.png

                                 

                                 

                                Q1 Store

                                the goal again is to identify if multiple employees are at the same store number at the same day & time, and/or If a single employee is reporting multiple shifts during the same day & overlapping time.

                                 

                                select t1.[Employee Id], t1.[Store Number], t1.[ShiftIn], t1.[ShiftOut]
                                     , t2.[Employee Id], t2.[Store Number], t2.[ShiftIn], t2.[ShiftOut]
                                from [Sheet1$] t1, [Sheet1$] t2
                                where t1.[Store Number] = t2.[Store Number]
                                and   t1.[ShiftIn]     <= t2.[ShiftOut]
                                and   t1.[ShiftOut]    >= t2.[ShiftIn]
                                and   t1.[Employee Id] <> t2.[Employee Id]
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                

                                 

                                Line 4 is a normal self "inner join" on [Store Number].

                                Line 5 to 7 check if shift overlaps other employees' shifts.

                                Lina 7 also excludes duplicate registrations and therefore no need of an extra condition for that.

                                 

                                 

                                Q2 Employee

                                the goal again is to identify if multiple employees are at the same store number at the same day & time, and/or If a single employee is reporting multiple shifts during the same day & overlapping time.

                                 

                                select t1.[Employee Id], t1.[Store Number], t1.[ShiftIn], t1.[ShiftOut]
                                     , t2.[Employee Id], t2.[Store Number], t2.[ShiftIn], t2.[ShiftOut]
                                from [Sheet1$] t1, [Sheet1$] t2
                                where t1.[Employee Id] = t2.[Employee Id]
                                and   t1.[ShiftIn]     < t2.[ShiftOut]
                                and   t1.[ShiftOut]    > t2.[ShiftIn]
                                and not (
                                  t1.[Store Number] = t2.[Store Number] and
                                  t1.[ShiftIn]      = t2.[ShiftIn]      and
                                  t1.[ShiftOut]     = t2.[ShiftOut]
                                  )
                                
                                
                                
                                
                                
                                
                                
                                
                                
                                

                                 

                                Line 4 is a normal self "inner join" on [Employee Id].

                                Line 5 and 6 check if shift overlaps other shifts for the same [Employee Id].

                                Lina 7 to 11 exclude duplicate registrations assuming they don't count as overlapping periods.

                                 

                                 

                                Ps. Provided SQLs use a combination of method 1 and 6 in  The Cross Join Collection.

                                A link is made to this comment under method 6.

                                 

                                 

                                Attached Workbook Version:  9.0