4 Replies Latest reply on Jan 14, 2019 9:38 AM by Ken Flerlage

date range falling into bins

Hey and thanks in advance.

I have a request which a few of us have been working on and yet to achieve.

we have date ranges:

start date / end date.

we have been tasked to create 5 min bins to measure the date range. we have the bins.

our problem is that for every 5 mins between start date and end date we need to flag the 5 min bin..

example:

 active calls Start time End time Call A 9:00 9:04 Call B 9:02 9:12 Call C 9:01 9:07 Call D 9:06 9:09 Call E 9:11 9:14 Call F 9:07 9:09

what we are attempting to create is this:

 5 min date range bins 9:00 - 9:05 9:06-9:10 9:11-9:15 Count of Active Calls 3 5 2 Call A Call B Call B Call B Call C Call C Call D Call E Call E Call F

with a final output that looks like this:

• 1. Re: date range falling into bins

You may follow this:

• 2. Re: date range falling into bins

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.

Include

// Determine if we should include or exclude the row based on times.

IF [Start]<=[Start time] AND [End]>=[Start time] THEN

"Include"

ELSEIF [Start]<=[End time] AND [End]>=[Start time] THEN

"Include"

ELSEIF [Start]>=[Start time] AND [End]<=[End time] THEN

"Include"

ELSE

"Exclude"

END

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!

1 of 1 people found this helpful
• 3. Re: date range falling into bins

for performance reasons, I used this query to pull the data based on your logic... again, thanks for your help.

select f.*,

CASE

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'

'Exclude'

include

from

select *

from

(select SessionIdTime,SessionIdSeq, InviteTime, EndTime, '1' as one_r

FROM [LcsCDR].[dbo].[VoipDetailsView]

WHERE InviteTime > DATEADD(hh, -24, GETDATE())

and EndTime is not null) as r

inner join

select b.START, DATEADD(mi, 5,   b.START) as EN,'1' as one

from

(select DATEADD(minute, a.S * 5, '2010-01-01T00:00:00') AS START

from

(select distinct datediff(minute, '2010-01-01T00:00:00', InviteTime)/5 as S

FROM [LcsCDR].[dbo].[VoipDetailsView]

WHERE InviteTime > DATEADD(hh, -24, GETDATE())) as a) as b ) as c

on r.one_r = c.one ) as f

1 of 1 people found this helpful
• 4. Re: date range falling into bins

That works too!!