Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Auto attendant and Call queue historical reports

Hi,

 

Just want to ask if anyone has the same issue where in the Agent Timeline, Call Queue/ CQ Name is showing Not found.

Anyone have resolved it? Ta

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Somehow resolved it by using the v1.64 script and replicate it to 3.2.1. Thanks!

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    Anonymous 

    Is CQ Name a measure ? Pls provide more details about this issue. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Ryan,

       

      In the agent tab, CQ Name is showing Not found. Not sure if there's something to be edited under fCallQueueAnalytics, but posting it below for your reference.

       

      CQ slicer

       

      Script

      let
      PerCall = List.Contains(ReportLevelsPerCallList, Language, Comparer.OrdinalIgnoreCase),
      NumberOfRecords = Table.RowCount(CallQueueAnalyticsRaw),
      ReportLevelLanguageCode = List.First(ReportParameters[ReportLanguageCode]),
          ReportByRAName = if List.Last(AuthorizedList[Diagnostics]) = "NOTAUTH" then
              Logical.FromText("TRUE")
          else
              Logical.FromText("FALSE"),
       
      Source = CallQueueAnalyticsRaw,
       
      #"AddReportLevelLanguageCode" = Table.AddColumn(#"Source", "LanguageCode", each ReportLevelLanguageCode),
        
      #"AddCQTargetType" = Table.AddColumn(#"AddReportLevelLanguageCode", "CQTargetType", each 
      if [CallQueueCallResult] = "callback_call_timed_out" then
        "Disconnect"
      else if [CallQueueCallResult] = "transferred_to_callback_caller" then
        "User"
      else
        [CallQueueTargetType]),
       
        //Convert UTC start & end fields appropriately
      #"CallStartUTC" = if PerCall then
      Table.AddColumn(#"AddCQTargetType", "CSTUTC", each DateTimeZone.FromText([UserStartTimeUTC]&" +00:00"))
      else
      #"AddCQTargetType",
       
      //#"CallEnd UTC" = if #"Report Level" = "Per Call" then
      #"CallEndUTC" = if PerCall then
      Table.AddColumn(#"CallStartUTC", "CETUTC", each DateTimeZone.FromText([EndTime]&" +00:00"))
      else
      #"CallStartUTC",
        
      //#"ChangeUTCType" = if #"Report Level" = "Per Call" then
      #"ChangeUTCType" = if PerCall then
      Table.TransformColumnTypes(#"CallEndUTC", {{"CSTUTC", type datetimezone}, {"CETUTC", type datetimezone}})
      else
      #"CallEndUTC",
        
      //#"Call Start Time Local" = if #"Report Level" = "Per Call" then
      #"CallStartTimeLocal" = if PerCall then
      Table.AddColumn(#"ChangeUTCType", "CallStartTimeLocal", each
      if #"UTC Offset" = "UTC-12:00" then DateTimeZone.SwitchZone([CSTUTC], -12)
      else if #"UTC Offset" = "UTC-11:00" then DateTimeZone.SwitchZone([CSTUTC], -11)
      else if #"UTC Offset" = "UTC-10:00" then DateTimeZone.SwitchZone([CSTUTC], -10)
      else if #"UTC Offset" = "UTC-09:30" then DateTimeZone.SwitchZone([CSTUTC], -9, -30)
      else if #"UTC Offset" = "UTC-09:00" then DateTimeZone.SwitchZone([CSTUTC], -9)
      else if #"UTC Offset" = "UTC-08:00" then DateTimeZone.SwitchZone([CSTUTC], -8)
      else if #"UTC Offset" = "UTC-07:00" then DateTimeZone.SwitchZone([CSTUTC], -7)
      else if #"UTC Offset" = "UTC-06:00" then DateTimeZone.SwitchZone([CSTUTC], -6)
      else if #"UTC Offset" = "UTC-05:00" then DateTimeZone.SwitchZone([CSTUTC], -5)
      else if #"UTC Offset" = "UTC-04:00" then DateTimeZone.SwitchZone([CSTUTC], -4)
      else if #"UTC Offset" = "UTC-03:30" then DateTimeZone.SwitchZone([CSTUTC], -3, -30)
      else if #"UTC Offset" = "UTC-03:00" then DateTimeZone.SwitchZone([CSTUTC], -3)    
      else if #"UTC Offset" = "UTC-02:00" then DateTimeZone.SwitchZone([CSTUTC], -2)
      else if #"UTC Offset" = "UTC-01:00" then DateTimeZone.SwitchZone([CSTUTC], -1)
      else if #"UTC Offset" = "UTC-00:00" then DateTimeZone.SwitchZone([CSTUTC], 0)
      else if #"UTC Offset" = "UTC+01:00" then DateTimeZone.SwitchZone([CSTUTC], +1)
      else if #"UTC Offset" = "UTC+02:00" then DateTimeZone.SwitchZone([CSTUTC], +2)
      else if #"UTC Offset" = "UTC+03:00" then DateTimeZone.SwitchZone([CSTUTC], +3)
      else if #"UTC Offset" = "UTC+03:30" then DateTimeZone.SwitchZone([CSTUTC], +3, +30)
      else if #"UTC Offset" = "UTC+04:00" then DateTimeZone.SwitchZone([CSTUTC], +4)
      else if #"UTC Offset" = "UTC+04:30" then DateTimeZone.SwitchZone([CSTUTC], +4, +30)
      else if #"UTC Offset" = "UTC+05:00" then DateTimeZone.SwitchZone([CSTUTC], +5)
      else if #"UTC Offset" = "UTC+05:30" then DateTimeZone.SwitchZone([CSTUTC], +5, +30)
      else if #"UTC Offset" = "UTC+05:45" then DateTimeZone.SwitchZone([CSTUTC], +5, +45)
      else if #"UTC Offset" = "UTC+06:00" then DateTimeZone.SwitchZone([CSTUTC], +6)
      else if #"UTC Offset" = "UTC+06:30" then DateTimeZone.SwitchZone([CSTUTC], +6, +30)
      else if #"UTC Offset" = "UTC+07:00" then DateTimeZone.SwitchZone([CSTUTC], +7)
      else if #"UTC Offset" = "UTC+08:00" then DateTimeZone.SwitchZone([CSTUTC], +8)
      else if #"UTC Offset" = "UTC+08:45" then DateTimeZone.SwitchZone([CSTUTC], +8, +45)
      else if #"UTC Offset" = "UTC+09:00" then DateTimeZone.SwitchZone([CSTUTC], +9)
      else if #"UTC Offset" = "UTC+10:00" then DateTimeZone.SwitchZone([CSTUTC], +10)
      else if #"UTC Offset" = "UTC+10:30" then DateTimeZone.SwitchZone([CSTUTC], +10, +30)
      else if #"UTC Offset" = "UTC+11:00" then DateTimeZone.SwitchZone([CSTUTC], +11)
      else if #"UTC Offset" = "UTC+12:00" then DateTimeZone.SwitchZone([CSTUTC], +12)
      else if #"UTC Offset" = "UTC+12:45" then DateTimeZone.SwitchZone([CSTUTC], +12, +45)
      else if #"UTC Offset" = "UTC+13:00" then DateTimeZone.SwitchZone([CSTUTC], +13)
      else if #"UTC Offset" = "UTC+14:00" then DateTimeZone.SwitchZone([CSTUTC], +14)    
      else DateTimeZone.SwitchZone([CSTUTC], 0))
      else
      // #"ChangeUTCType",
      Table.AddColumn(#"ChangeUTCType", "CallStartTimeLocal", each DateTimeZone.FromText([Date]&" +00:00")),
       
      //#"Call End Time Local" = if #"Report Level" = "Per Call" then
      #"CallEndTimeLocal" = if PerCall then
      Table.AddColumn(#"CallStartTimeLocal", "CallEndTimeLocal", each 
      if #"UTC Offset" = "UTC-12:00" then DateTimeZone.SwitchZone([CETUTC], -12)
      else if #"UTC Offset" = "UTC-11:00" then DateTimeZone.SwitchZone([CETUTC], -11)
      else if #"UTC Offset" = "UTC-10:00" then DateTimeZone.SwitchZone([CETUTC], -10)
      else if #"UTC Offset" = "UTC-09:30" then DateTimeZone.SwitchZone([CETUTC], -9, -30)
      else if #"UTC Offset" = "UTC-09:00" then DateTimeZone.SwitchZone([CETUTC], -9)
      else if #"UTC Offset" = "UTC-08:00" then DateTimeZone.SwitchZone([CETUTC], -8)
      else if #"UTC Offset" = "UTC-07:00" then DateTimeZone.SwitchZone([CETUTC], -7)
      else if #"UTC Offset" = "UTC-06:00" then DateTimeZone.SwitchZone([CETUTC], -6)
      else if #"UTC Offset" = "UTC-05:00" then DateTimeZone.SwitchZone([CETUTC], -5)
      else if #"UTC Offset" = "UTC-04:00" then DateTimeZone.SwitchZone([CETUTC], -4)
      else if #"UTC Offset" = "UTC-03:30" then DateTimeZone.SwitchZone([CETUTC], -3, -30)
      else if #"UTC Offset" = "UTC-03:00" then DateTimeZone.SwitchZone([CETUTC], -3)
      else if #"UTC Offset" = "UTC-02:00" then DateTimeZone.SwitchZone([CETUTC], -2)
      else if #"UTC Offset" = "UTC-01:00" then DateTimeZone.SwitchZone([CETUTC], -1)
      else if #"UTC Offset" = "UTC-00:00" then DateTimeZone.SwitchZone([CETUTC], 0)
      else if #"UTC Offset" = "UTC+01:00" then DateTimeZone.SwitchZone([CETUTC], +1)
      else if #"UTC Offset" = "UTC+02:00" then DateTimeZone.SwitchZone([CETUTC], +2)
      else if #"UTC Offset" = "UTC+03:00" then DateTimeZone.SwitchZone([CETUTC], +3)
      else if #"UTC Offset" = "UTC+03:30" then DateTimeZone.SwitchZone([CETUTC], +3, +30)
      else if #"UTC Offset" = "UTC+04:00" then DateTimeZone.SwitchZone([CETUTC], +4)
      else if #"UTC Offset" = "UTC+04:30" then DateTimeZone.SwitchZone([CETUTC], +4, +30)
      else if #"UTC Offset" = "UTC+05:00" then DateTimeZone.SwitchZone([CETUTC], +5)
      else if #"UTC Offset" = "UTC+05:30" then DateTimeZone.SwitchZone([CETUTC], +5, +30)
      else if #"UTC Offset" = "UTC+05:45" then DateTimeZone.SwitchZone([CETUTC], +5, +45)
      else if #"UTC Offset" = "UTC+06:00" then DateTimeZone.SwitchZone([CETUTC], +6)
      else if #"UTC Offset" = "UTC+06:30" then DateTimeZone.SwitchZone([CETUTC], +6, +30)
      else if #"UTC Offset" = "UTC+07:00" then DateTimeZone.SwitchZone([CETUTC], +7)
      else if #"UTC Offset" = "UTC+08:00" then DateTimeZone.SwitchZone([CETUTC], +8)
      else if #"UTC Offset" = "UTC+08:45" then DateTimeZone.SwitchZone([CETUTC], +8, +45)
      else if #"UTC Offset" = "UTC+09:00" then DateTimeZone.SwitchZone([CETUTC], +9)
      else if #"UTC Offset" = "UTC+10:00" then DateTimeZone.SwitchZone([CETUTC], +10)
      else if #"UTC Offset" = "UTC+10:30" then DateTimeZone.SwitchZone([CETUTC], +10, +30)
      else if #"UTC Offset" = "UTC+11:00" then DateTimeZone.SwitchZone([CETUTC], +11)
      else if #"UTC Offset" = "UTC+12:00" then DateTimeZone.SwitchZone([CETUTC], +12)
      else if #"UTC Offset" = "UTC+12:45" then DateTimeZone.SwitchZone([CETUTC], +12, +45)
      else if #"UTC Offset" = "UTC+13:00" then DateTimeZone.SwitchZone([CETUTC], +13)
      else if #"UTC Offset" = "UTC+14:00" then DateTimeZone.SwitchZone([CETUTC], +14)    
      else DateTimeZone.SwitchZone([CETUTC], 0))
      else
      // #"CallStartTimeLocal",
      Table.RenameColumns(#"CallStartTimeLocal", {{"Date", "DateOrig"}}),
       
      #"ChangeLocalType" = if PerCall then
      Table.TransformColumnTypes(#"CallEndTimeLocal",{{"CallStartTimeLocal", type datetimezone}, {"CallEndTimeLocal", type datetimezone}})
      else
      Table.TransformColumnTypes(#"CallEndTimeLocal",{{"CallStartTimeLocal", type datetimezone}}),
       
      #"CallStartDateLocal" = Table.AddColumn(#"ChangeLocalType", "CallStartDateLocal", each Date.From([CallStartTimeLocal])),
       
      #"DateOnly" = Table.AddColumn(#"CallStartDateLocal", "Date", each Text.Combine({Text.Combine({Text.From(Date.Year([CallStartTimeLocal])), Text.From(Date.Month([CallStartTimeLocal])), Text.From(Date.Day([CallStartTimeLocal]))}, "-"), Text.From(Time.Hour([CallStartTimeLocal]))&":00:00"}," ")),
      #"ChangeDateOnly" = Table.TransformColumnTypes(#"DateOnly",{{"CallStartDateLocal", type date}, {"Date", type datetime}}),
       
      #"DuplicateCQConnectivityType" = Table.DuplicateColumn(#"ChangeDateOnly", "PSTNConnectivityType", "CQConnectivityTypeCode"),
      #"ReplaceCallingPlan"   = Table.ReplaceValue(#"DuplicateCQConnectivityType","CallingPlan","8600",Replacer.ReplaceValue,{"CQConnectivityTypeCode"}),    
      #"ReplaceDirectRouting" = Table.ReplaceValue(#"ReplaceCallingPlan","DirectRouting","8601",Replacer.ReplaceValue,{"CQConnectivityTypeCode"}),    
      #"ReplaceOperatorConnect" = Table.ReplaceValue(#"ReplaceDirectRouting","OperatorConnect","8602",Replacer.ReplaceValue,{"CQConnectivityTypeCode"}),
      #"ReplaceACSCall" = Table.ReplaceValue(#"ReplaceOperatorConnect","ACS Call","8610",Replacer.ReplaceValue,{"CQConnectivityTypeCode"}),    
      #"ReplaceBlankCQConnectivityTypeCode" = Table.ReplaceValue(#"ReplaceACSCall","","8620",Replacer.ReplaceValue,{"CQConnectivityTypeCode"}),
       
      #"ReplaceBlankCQTargetType" = Table.ReplaceValue(#"ReplaceOperatorConnect","","Disconnect",Replacer.ReplaceValue,{"CQTargetType"}),
      #"ReplaceBlankTotalCallCount" = Table.ReplaceValue(#"ReplaceBlankCQTargetType","",0,Replacer.ReplaceValue,{"TotalCallCount"}),
       
      #"CallCountAbandonded" = Table.AddColumn(#"ReplaceBlankTotalCallCount", "CQCallCountAbandoned", each
      if [CallQueueCallResult] = "disconnected" and [CQTargetType] = "Disconnect" then
      1
      else
      0),
        
       
      #"Hour" = Table.AddColumn(#"CallCountAbandonded", "CQHour", each Time.Hour([Date])),
       
      #"CQRAName" = Table.AddColumn(#"Hour", "CQRAName", each List.First(Text.Split([CallQueueIdentity], "@"))),
       
          #"CQJoinName" = if ReportByRAName then
              #"CQRAName"
          else
              Table.NestedJoin(#"CQRAName", {"CallQueueId"}, AuthorizedListCQ, {"CallQueueId"}, "CallQueueIdName", JoinKind.LeftOuter),
       
          #"CQName" = if ReportByRAName then
              Table.AddColumn(#"CQJoinName", "CallQueueName", each "")
          else
              Table.ExpandTableColumn(#"CQJoinName", "CallQueueIdName", {"CallQueueName"}),
       
          #"CQSlicer" = Table.AddColumn(#"CQName", "CQSlicer", each
              if ReportByRAName or [CallQueueName] = null then
                  [CQRAName]
              else
                  [CallQueueName]),
       
      #"MergedColumns" = Table.AddColumn(#"CQSlicer", "DateTimeCQName", each Text.From([Date]) & [CQRAName]),
          

       

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        1 year ago

        Anonymous 

        I suggest you to check step by step. So not found display for the last step?