Calculation Problems

acombest19 · ‎08-19-2010

Okay, I'm having a problem.

Using Microsoft Access and ODBC connectivity to the queries on the AWDB, I have been able to successfully re-create the Agent Consolidated Daily Report.

Now I am trying to re-create the Agent Team Consolidated Daily Report and my time calculations are just not giving me the same numbers as the Cisco WebView report.

I've attached screen prints.

Any help would be greatly appreciated!!!!

BTW, I am doing this because not everyone in my organization has access or knowledge of SQL, and there are other reports our end users want that are not currently available from WebView. I am re-creating these as learning tools to familiarize myself with the data and the different calculations.

Thank you,

Angie Combest

acombest@clarian.org

Martin Connolly · ‎08-19-2010

Hi Angie,

Looking at the figures in your attachment it seems to be a rounding error as there's only a difference of a second between your IncomingHandledAHT figure and the WebView Incoming AHT figure, It looks like your query is rounding up the result to full seconds, whereas the WebView report is rounding down.

Regards,

Martin Connolly

Dimension Data UK

acombest19 · ‎08-19-2010

I don't think it's rounding. Otherwise, none of my time calculations would match. Most of them do, but unfortunately, not all of them do.

Also, when I look at the seconds on the lines that don't match, they don't round up (Example: 466.9722222 is rounded to 466.97).

I even changed my calculation to see if that was it. I get the same results.

The new calc I tried:

IIf((Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!CallsHandledToHalf))>0,

(Format(Int(((Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!HandledCallsTimeToHalf))/(Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!CallsHandledToHalf)))/3600),"00") & ":" & Format(Int((((Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!HandledCallsTimeToHalf))/(Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!CallsHandledToHalf)))-(Int(((Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!HandledCallsTimeToHalf))/(Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!CallsHandledToHalf)))/3600)*3600))/60),"00") & ":" & Format(((((Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!HandledCallsTimeToHalf))/(Sum(AWDB_Q_Agent_Skill_Group_Half_Hour!CallsHandledToHalf))) Mod 60)),"00")),"00:00:00")

acombest19 · ‎08-19-2010

I'm going to try removing all decimal places and see what happens....... Will let you know.

Thanks!!!!!!!!!!

acombest19 · ‎08-20-2010

Thank you!!!!

If I remove all decimals without rounding up or down, it seems to be working!!!!!

Thank you, thank you, thank you!!!!!!!!!!!!!!!!!!!!

Nathan Luk · ‎08-19-2010

Hi Angie,

In the presentation layer, that report displays AHT with the following calculation:

if (len(string(int(AHT /3600)) )= 1, right("0" + string(int(AHT /3600)), 2) ,string(int(AHT /3600) ) ) + ":" + right("0" + string(int(mod(AHT, 3600) / 60)), 2) + ":" + right("0" + string(int(mod(AHT , 60))), 2)

And in the SQL query backend for that report the calc is:

AHT = ISNULL(AgentSkillGroup.handletime / AgentSkillGroup.CallsHandled, 0),

So maybe you can give that a try to see if that gives you the same result?

Cheers,

Nathan

acombest19 · ‎08-20-2010

Thanks so much for your help!!!!

Thx - A

acombest19 · ‎12-22-2010

Thank you!!!!!!