Replacing Line Chart with a KPI Clock Chart (Evolution of a requirement)

    Hi all,

     

    I wanted to share with you a recent experience I had with a client who ran a 24 hour call centre and was looking to create a dashboard for their agents to see the expected call volumes for each hour during the day. They used data from the previous 30 days to try and predict how many calls there were going to receive over the course of the day.

     

    The first approach used was a Line Chart for the 24 hour periods:

    Calls.PNG

    The problem with this was that some agents seemed to plan their day around the avoidance of busy periods. To try and combat this, the client changed the requirement to show the volume of calls vs. the agents answering them. They hoped that the troublesome agents would see how their approach to work was impacting their colleagues and that they would adjust their behaviour, without an intervention from management.

     

    The result was a Combo Chart:

    Calls vs Agents.PNG

    Unfortunately, this did not modify the targeted agent's attitude. Management was required to intervene and the chart now caused resentment between co-workers who were taking the bulk of the calls during busy periods. We needed to come up with a different approach that could reach agents; giving them enough information to show when they are needed the most whilst at the same time, not giving them so much information that could use that to exploit the system. Here is what we came up with.

     

    Clock Heat-map Chart / Clock KPI Chart

     

    We created a heat-map shaped like a clock that shows the busiest times in darker shades of green. Using this approach, agents were no longer able to see any sharp spikes in call volumes, only subtle changes in colour. As the app was set to reload every minute, the Minute/Hour hands were calculated using ReloadTime() instead of Now() to avoid any performance issues. These hands also turn grey and reset to 00:00 (AM) & 12:00 (PM) depending on the current time of day.

    Clock Heat Map.PNGEven with this approach, some problems still remained due to the fact that they could still read the number of calls to expect during a given hour. This was combated by removing the values on data points.

    Clock Heat Map - No Numbers.PNG

    The heat map seemed to help but we eventually decided to move away from the heat-map approach to a simple "is the KPI met?" approach. In other words, show teams if they were meeting their KPIs for a month.

    Clock KPIs.PNG

    This proved the most effective as bonuses are distributed to teams that met their KPIs (more than 50% calls answered) so agents were now encouraged to work harder during busy periods of the day. We supplied the agents with the information that impacted them the most and nothing else. Rogue agents could no longer leverage the information provided to them with for their own gains and managers could still see actual figures (%) by hovering over a segment.

     

    To Create Clock Charts

     

    To create the chart, follow these steps.

     

    1. Create a Pie Chart
    2. Do not add any Dimensions
    3. Create 12 Expressions per chart (one for each hour), looking something like this:

    Clock Expressions.PNG

    This example is for the 12th hour of the day. You must change the hour in set analysis for each expression definition. The Dual() function is used to display a value to the end user (first line) and create the width of each segment (second line). I used an if statement to display blank values if Sum(Calls)=0. This is a personal preference and can be ignored (I just think it looks cleaner than 0). The second line of Dual() should always be set to 1 here (12 segments = 1 creates 12 segments of the same length).

     

    Dual(
      if(Sum({<Hour = {12}>} Calls)=0,' ',Sum({<Hour = {12}>} Calls))
      ,1
    )
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

        4. Expand the + icon for each expressions and highlight Background so we can set the colours of each segment. In the definition, use one of the following, depending on what you are looking for:

     

    Heat-map:

     

    if(Sum({<Hour = {12}>} Calls)=0,
      cGray_Light,
      ColorMix1(Sum({<Hour = {12}>} Calls) / MAX(Aggr(Sum({<Hour = {12,13,14,15,16,17,18,19,20,21,22,23}>} Calls), Hour)), cColorMix_LightGreen, cColorMix_DarkGreen)
    )
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

    KPI (Red-Green):

     

    if(Sum({<Hour = {12}>} Calls)=0,
      cGray_Light,
      if(Sum({<Hour = {12}>} Answered)/Sum({<Hour = {12}>} Calls)>.5,
      cGreen,
      cRed
    ))
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

        5. In the Colors tab, set the Transparency of the Fame Background to 100%.

        6. In the Layout tab, set the Layer to Top. The chart needs to be above the clock hands so it is not obscured when hovering over.

     

    Tip: When creating these pie charts and using Values on Data Points, always make the charts are wider than they are higher. This will prevent the chart from changing size depending on the values displayed; otherwise, larger figures on data points will force the pie chart to shrink in order to fit into it's box.

     

    To Create Clock Hands


    Hour Hand:

     

    1. Create Gauge Chart.
    2. Set the Dimension =1 (Add Calculated Dimension).
    3. For the PM clock (see attached document for AM), set the Expression to the following. This sets the hour to 12:00 if the time is between 00:00 and 12:00:

     

    =if(Hour(ReloadTime())<12,
      0,
      if(ReloadTime()-Floor(ReloadTime())>0.5,
      (ReloadTime()-0.5)-Floor(ReloadTime()),
      ReloadTime()-Floor(ReloadTime())))
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

        4. In the Style Tab, set the Look to:

    Style.PNG

        5. In the Presentation Tab, use the following settings:

    Presentation.PNGThe expression for the colour of the Indicator is as follows:

     

    if(Hour(ReloadTime())<12, cGray_Dark, Black())
    
    
    
    
    
    
    
    
    
    
    
    
    
    

     

        6. In the Colors tab, set the Transparency of the Fame Background to 100%.

        7. In the Layout tab, set the Layer to Middle.

     

    Minute Hand:


    Copy the above steps except for the Expression & Presentation tabs. Below is the new Expression :

     

    =if(Hour(ReloadTime())<12,
      0,
      Minute(ReloadTime())/60)
    
    
    
    
    
    
    
    

     

    And here is the Presentation tab:

    Presentation 2.PNG

    Set the Layer to Bottom and now carefully resize & align all objects to they appear like any of the clocks above.

     

    Please see the attached file with dummy data for more details on how to create Pie Chart Clocks.

     

    Your feedback is more than welcome and let me know if you think I left anything out of this tutorial.