1 Reply Latest reply on Feb 27, 2017 7:42 AM by GreggSmith

    Update Date Filter Calculation Discrepancies

    cmajewsk Apprentice

      I have a scorecard view of a various calculated series' saved with a Current Quarter date filter. If I Update Date Filter with Last Quarter, is the calculated series now showing me an average of the three months in the quarter? How is it taking all 3 months worth of data and providing me the calculation? The reason I ask is because if I take the calculation for each month individually and divide it by 3 I get different values than if I select the Last Quarter date filter.

       

      Tickets Open = A

      Tickets Closed within SLA = B

      Calculation = IF(A=0,0,(B/A)*100)

       

      So for example, I update the date filter to Last Quarter

       

      And I get the following value:

       

      However, if I take a static date for each month over the course of that last quarter (OCT: 96.1; NOV: 94.7; DEC: 93.9) I get 94.9. If I take the static dates from OCT 1 - DEC 31 I get the 95.1 value like I would with the Last Quarter date filter.

       

      Now while this is not a large variance, it is enough to raise questions on how the built in calculation is being done and why the values are different.

        • 1. Re: Update Date Filter Calculation Discrepancies
          GreggSmith SupportEmployee

          I apologize for not answering this sooner.  This is not a bug.  The two math calculations performed (an average of three months' tickets and the average of 3 1-month averages) are not the same calculation and will usually result in different answers.

           

          Here is an extreme example of the same scenario to make the difference obvious:

           

          Month 1 = 1 ticket w/ 1 Within Target = 100% Within Target

          Month 2 = 1 ticket w/ 1 Within Target = 100% Within Target

          Month 3 = 98 tickets w/ 1 Within Target = 1% Within Target

           

          So, evaluated separately, Month 1 is 100%, Month 2 is 100% and Month 3 is 1%.

           

          When you have Xtraction calculate a single percentage for the 3-month span, it is calculating 100 tickets w/ 3 tickets within Target (total tickets for 3 months and total tickets within target for the 3 months).

          In this example, Xtraction would return 3% in the scorecard.

           

          When double-checking the number, you took the average of the averages (100% + 100% + 1%) / 3 = 67%.

           

          The problem with taking the average of the averages is that you give equal weight to each month's average, but the months have differing numbers of tickets.  The 1 ticket in Month 1 has as much impact on the average as all 98 tickets in Month 3 combined.  This leads to the 67% answer.  When you group all three month's numbers together (total tickets and total tickets within target), each ticket has equal effect on the average.

           

          Hopefully, this was helpful in explaining the problem.  Let me know if you have any further questions.

           

          Thanks,

          Gregg