Need to chart 2 timestamps for a status time and graph the whole day

jeremymcd ⚪️
edited February 16 in Card Building

I am looking for ways to take 2 timestamps, a start, and an end timestamp, and chart them to account for a user's activity that day. I am using call center agent status times and want to have a bar chart for each agent that shows their status for the day and then plot in the calls that came in with wait time and talk time. What would be the best way to go about building this? Can I do it at the chart level or will I need to use ELT?

Here is a sample of the data I want to chart

Best Answers

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    Hi @jeremymcd

    You'd need to utilize an ETL to format your data such that you have a record for each hour of the day instead of a record for each status.

    For example (this is assuming you're only looking at a single day otherwise you'd need to include your date in here as well):

    HOUR | Status | Agent |Seconds |

    12 | Admin Work | Test Agent | 789

    12 | Available | Test Agent | 20

    12 | On A Call | Test Agent | 2001


    You'd need to keep an eye out for your statuses that span hours (like the Lunch hour in your example) so that you're including those in both buckets.

    You'd likely need a hour / time dimension table to left join your dataset to, then filter your dataset such that the hour from the dimension table is between your start and end hour. Then calculate the number of seconds to be the lesser of the end time and the HOUR:59:59 (HOUR being the current hour you're comparing against) and the end time. Take that time and subtract the greater of the HOUR:00:00 time (again HOUR being the current hour you're comparing against) and the start time.

    For this example:

    Lunch | 16:06:00 | 17:17:07:26

    HOUR 16:

    16:59:59 - 16:06:06 = 3233 seconds

    HOUR 17:

    17:17:07 - 17:00:00 = 1207 seconds

  • jeremymcd
    jeremymcd ⚪️
    edited February 18 Accepted Answer

    Thank you both for the updated information, I will get to work on creating this.


  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @jeremymcd

    You can utilize some beast modes on the chart to calculate the number of seconds between the timestamps. I typically use the UNIX_TIMESTAMP and basic math to calculate the difference.

    UNIX_TIMESTAMP(`status_start_at 1`) - UNIX_TIMESTAMP(`status_end_at 1`)

    You can then group by the `user_name 1` field and the `status_label 1` field and then SUM this beast mode to get the total seconds for each. If you want to break it down by each day then you'd want to group based on the `status_start_at 1` field and use the Domo Date selection tool (upper right of your chart) to chart by day / week / month etc.

    Alternatively yes, you could utilize an ETL to calculate this ahead of time if you prefer to go that route as well.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @jeremymcd absolutely +1 to @GrantSmith 's answer.

    Side note Grant is the community expert on dealing with Time in Dojo, and I find his recommendation to convert to Unix_Timestamp with timediff related issues to be the easiest way to skip some of the shortcomings of Domo's other time comparison implementations.

    Regarding "should i do it in ETL vs. Analyzer"

    Yes, Analyzer is the fast and dirty solution. building this particular transfrom into ETL is probably the wiser solution when you consider scalability. During ETL you can rename columns so they are easier to use, AND call duration is a standard transform that's not impacted by business logic. it's just column A - column B. For fixed transforms like this, just bake it into your ETL for ease of use. particularly if you might want to recycle that metric in another card or dataflow.

  • @GrantSmith @jaeW_at_Onyx Thank you for the quick replys and information on using the Unix_timestamp. What you suggested does work for creating a chart with the times stacked for each agent's time but is there a way to display them as they happened to recreate the day's events in a visual form? I would like the bar chart for each agent to show the status for the # of seconds they were in that status during the corresponding time of day and not as just a sum. I have tried to set the Y as a range from 00:00:00 to 23:59:59 but it does not start the graphing at the user's first status time. Is it possible to graph these results on the chart corresponding to the time they actually occurred?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    it would be a pita trying to get everyone's time entry to line up.

    if it were my task i would create a dataset with one row per half hour per day (48 rows) * 7days * number of employees

    then for each block i'd add attributes "what where they doing" and busy or not.

    Then put it in a heat map.