I need help visualizing timestamps

SeanPT
SeanPT Contributor
in Charting

I have ~ 60 million records of timestamps (HH:MM) and I want to see how those records are distributed through out the day.

 

I did a line graph with the timestamps on the X axis and the count of each timestamp on the Y and that worked a bit. I had to disable a bunch of stuff because of the volume of records.

 

But I feel like there would be a better way. I'm open to suggestions.

Best Answer

  • ckatzman
    ckatzman Contributor
    Answer ✓

    I create quite a few heat map cards for looking at HOUR of day on the vertical, and a 30-day rolling on the horizontal, representing different types of transactions or processes.  Works great to see if there are clusters within certain blocks of time; helps drive staffing schedules.  Obviously you could turn HOUR into 30 minute blocks, just have to recalculate them into something that makes sense to show 48 instead of 24... maybe just do HH.5 as an equivalent to HH:30.

     

    Think also, if this impacts you, about your timestamps themselves... do they all represent the same timezone?  Do you have a central server but timestamps are decentralized based on transaction location, or is everything recorded in the same basis?  

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"

Answers

  • swyatt
    swyatt Contributor

    What about a heat map? The 1st category can be the hours and the 2nd (if possible) could be the day of week, with the values as a "count" aggregation. A histogram would also be a decent option. I'm not sure what the timestamps represent, but if you're trying to determine which hour or minute segment provides the most values a Pareto is great. It's primarily use to visualize the "80-20" rule.

  • SeanPT
    SeanPT Contributor

    I'll give a heat map a try.

     

    The time stamps represent when a sale was made, e.g. 11:15 am. With 37 million records, every minute from open to close is accounted for with at least a few thousand sales.


    I think to do the time as a heat map I'm going to have to group into 30min chunks.

     

    That Pareto is a great idea because after looking at the data I think we definately fall in that 80-20.

  • jporras
    jporras Member

    Have you considered drillthru. 

     

    Remember, for reporting you start at a higher level and then drillthru.

     

    For example, I would create a data set by BUCKETS. These buckets could be, Morning, Afternoon, Night OR every 2 hours. Regardless of what card you're using. You could try bar chart or heatmap by the hour or bucket of 2 hours and then drill thru to the hour, then drill thru to the minute.

     

    Makes sense?

     

    Let me know if you need further clarification or help.

     

    regards

    JP

  • ckatzman
    ckatzman Contributor
    Answer ✓

    I create quite a few heat map cards for looking at HOUR of day on the vertical, and a 30-day rolling on the horizontal, representing different types of transactions or processes.  Works great to see if there are clusters within certain blocks of time; helps drive staffing schedules.  Obviously you could turn HOUR into 30 minute blocks, just have to recalculate them into something that makes sense to show 48 instead of 24... maybe just do HH.5 as an equivalent to HH:30.

     

    Think also, if this impacts you, about your timestamps themselves... do they all represent the same timezone?  Do you have a central server but timestamps are decentralized based on transaction location, or is everything recorded in the same basis?  

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
  • SeanPT
    SeanPT Contributor

    The heat map idea is great. That worked out really well. Thank you for the suggestion on that. I went with 15 minute blocks and it is some great data viz.

     

    On the timestamps, they are all 'local time' which is what I want. Of course in Workbench I flag them all as Central and in Domo I have central time zone selected. I have the suggestion out there on 'don't touch my timestamps' so hopefully that's something that we get at some point.

This discussion has been closed.

Categories