I actually just did something like this at work, though I did so in a data prep/ETL tool instead of Tableau, but the logic is basically the same. Start out by doing a cross-join between your two tables. Essentially this will join each record in the calls table to each one in the bins table. Cross-joins aren't an option in Tableau so use a 1=1 join calculation.
Note: This will artificially explode the number of records in your data set. In the small sample data set, I have 6 calls and 3 time bins. The join will multiply these to create 18 records.
Next, create a calculated field that will check the start/end times of each call against the start/end time of the bins and determine which calls fall within those bins.
// Determine if we should include or exclude the row based on times.
IF [Start]<=[Start time] AND [End]>=[Start time] THEN
ELSEIF [Start]<=[End time] AND [End]>=[Start time] THEN
ELSEIF [Start]>=[Start time] AND [End]<=[End time] THEN
Here's a table showing all the records with their include/exclude flags.
Next, I'll create a new sheet and add a filter on the Include flag, only keeping the value "Include". Now you can build a view to sum up the number of records, which should result in the correct count.
You could also do this in custom SQL, if you're connecting to a database that supports SQL. I don't like custom SQL particularly, but the advantage of this approach would be that the data would be filtered out before Tableau has to deal with it and you wouldn't need the include flag. If you're interested, I'm happy to show you that method as well.
See attached workbook.
If this answers your question, please be so kind as to mark this as the "correct answer" so we can close this thread and so others can quickly find the answer to similar questions in the future. Thanks!
Time Bins.twbx 18.6 KB
1 of 1 people found this helpful
for performance reasons, I used this query to pull the data based on your logic... again, thanks for your help.
f.START <=f.InviteTime and f.EN>=f.InviteTime then 'Include'
f.START<=f.EndTime and f.EN>=f.InviteTime then 'Include'
f.START>=f.InviteTime and f.EN <=f.EndTime then 'Include'
(select SessionIdTime,SessionIdSeq, InviteTime, EndTime, '1' as one_r
WHERE InviteTime > DATEADD(hh, -24, GETDATE())
and EndTime is not null) as r
select b.START, DATEADD(mi, 5, b.START) as EN,'1' as one
(select DATEADD(minute, a.S * 5, '2010-01-01T00:00:00') AS START
(select distinct datediff(minute, '2010-01-01T00:00:00', InviteTime)/5 as S
WHERE InviteTime > DATEADD(hh, -24, GETDATE())) as a) as b ) as c
on r.one_r = c.one ) as f
That works too!!