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

# Flag for Overlapping Time Period

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

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

• ###### 3. Re: Flag for Overlapping Time Period

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

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

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

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

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

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

Can I assume my attached did not help then?

Steve

• ###### 9. Re: Flag for Overlapping Time Period

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

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):

Attached Workbook Version:  9.0.

• ###### 10. Re: Flag for Overlapping Time Period

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

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:

• ###### 12. Re: Flag for Overlapping Time Period

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':

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.

Attached Workbook Version:  9.0