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

jeremymcd
jeremymcd Member
edited February 2021 in Charting

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

image.png


Best Answers

  • GrantSmith
    GrantSmith Coach
    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

    etc.


    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jeremymcd
    jeremymcd Member
    edited February 2021 Answer ✓

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

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jeremymcd
    jeremymcd Member

    @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?

  • GrantSmith
    GrantSmith Coach
    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

    etc.


    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jeremymcd
    jeremymcd Member
    edited February 2021 Answer ✓

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

  • pstrauss
    pstrauss Member

    I've got a similar challenge here:

    https://dojo.domo.com/main/discussion/57611/creating-a-heat-map-based-on-start-and-end-times

    Assuming I'll need to create a table with 30-minute increments of time and figure out a way to join to that in an ETL to generate a row for each 30-minute block of time?

Categories