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

    date range falling into bins

    robin smith

      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 callsStart timeEnd time
      Call A9:009:04
      Call B9:029:12
      Call C9:019:07
      Call D9:069:09
      Call E9:119:14
      Call F9:079:09

       

      what we are attempting to create is this:

      5 min date range bins9:00 - 9:059:06-9:109:11-9:15
      Count of Active Calls352
      Call A
      Call BCall BCall B
      Call CCall C
      Call D
      Call ECall E
      Call F

       

      with a final output that looks like this:

        • 2. Re: date range falling into bins
          Ken Flerlage

          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
            robin smith

            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
              Ken Flerlage

              That works too!!