2 Replies Latest reply on Jan 8, 2018 9:39 AM by matthew Blanch

    How Can  I Group Date Time by 5 Minute Intervals ?

    matthew Blanch


      I have an issue trying to create a graph resulting from my date/time format.


      Currently my date time format is as such:



      I am trying to generate a graph which will show  a Count of another ID field but i need this count at 5 min intervals. I need to keep the Date in the string as if i simply truncate the date to minutes it will aggregate the counts for the same minutes on other days. And i Want to be able to show multiple days on my X-axis.


      My current graph looks like this:




      I then need to create a Boolean to store the ID which i am not sure how to do. So for example if the ID is present at "Date 7:55 AM" , but there is no record at the subsequent "Date 8:00 AM" I would like for it to keep the count and not eliminate it for the next timeframe.If the ID is then present at "Date 8:05 AM" then it will take the new record. This is so it smoothes the datat so its not as jumpy.

        • 1. Re: How Can  I Group Date Time by 5 Minute Intervals ?
          Norbert Maijoor

          Hi Matthew,


          Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.



          1. D1. 5 minute interval: DATETIME(INT(FLOAT([Datetime])*288)/288)


          2. P1. Count Visit ID: count([Visit ID])


          3. Drag required objects to the indicated locations.





          • 2. Re: How Can  I Group Date Time by 5 Minute Intervals ?
            matthew Blanch

            Hi Norbert

            The Float worked to get the times in line. Thank you. The issue I still have is with the count.  For example i think it is looking at each specific float time interval to see if the ID is present. If the ID is not present at the time I still need to it include it in the count until it sees the next time frame where the ID is actually present.

            For example:

            This is what it looks like it is currently doing:


            ID               Float Time Calc               Count(ID)

            1234          7:00                                        1

            1234          7:05                                        1

            *No entry   7:10                                        0 <<Since there no entry for this time frame for the specified ID it will not include it in the total count when i add multiple ID's.

            1234          7:15                                        1




            An example is below, where we look into the time interval it is missing the record at 720, so then there is no count.

            I need a way to create the 5 min intervals along the x-axis, but if there is no record I still want it to exist, otherwise I cant create a calculation for the count at that time. For example if you look at below – At 715 there is a record and at 725 there is a record so it shows the associated count of the ID. But at 720 there is no record so it doesn’t plot anything… Is there a simple way to handle this?