Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Pavol Procka
    Daily Responses Sent - only display totals
    Topic posted August 8, 2018 by Pavol ProckaSilver Crown: 22,500+ Points 
    32 Views, 3 Comments
    Title:
    Daily Responses Sent - only display totals
    Content:

    Hi,

     

    I have a report showing number of emails answered from each Q yesterday and how many of them were answered within 24 hrs. As you can see in the attached, the report still shows one line for each incident, I however only need to see the totals.

    I have highlighted the fields I need to keep in green and the ones I don't want crossed out with red.

    Also attached the report definition.

    Anyone any ideas?

    Many thanks

    Pavol

    Version:
    18A
    Image:

    Comment

     

    • Jess Campbell

      Hi Pavol,

      I can't open the xml, but if you post a text version of the definition I would love to look at it.

      Without looking, I believe you may need to get rid of the reference number column and just count the reference numbers in the calculation for your under 24 hours column.

      Jess

       

       

    • Pavol Procka

      Hi Jess,

      attached is the definition PDF. When I remove the reference number column, I will lose the total responses sent (I need both total and how many of them within 24 hrs).
      If I remove the other columns (Response Sent, Time in Q, Action) the under 24 hrs column calculation stops working and just says 1 or 0.

    • Jess Campbell

      Well, you are going to get a separate line for each incident because of the response sent column, as that date will be different for each incident.

      We don't use any transaction based response rate calculations in my org, but the ones we use with other "aging" type reports keep the calculations within the column.

      like this: count(distinct if(date_diff(yourdate1, yourdate2) < 86400,incidents.ref_no))

      or this: Count(distinct If(If(incidents.initial_soln IS NOT NULL, date_diff(to_date(date_format(sysdate(),'DD/MM/YYYY HH:MI:SS AM'),'DD/MM/YYYY HH:MI:SS AM'),to_date($updated,'DD/MM/YYYY HH:MI:SS AM')),date_diff(to_date(date_format(sysdate(),'DD/MM/YYYY HH:MI:SS AM'),'DD/MM/YYYY HH:MI:SS AM'),to_date($datestart,'DD/MM/YYYY HH:MI:SS AM')))<= 14400,incidents.ref_no))

      In that one, date start is a variable that adjusts the dates based on working hours, but it does not get its own column, so it does not end up producing extra rows. It is only used in other column calculations.

      For reference: if(date_format(incidents.created, 'DAY')='Monday'&to_number(date_format(incidents.created, 'HH24'))IN(9,10,11,12,13,14,15,16,17,18,19,20),date_format(incidents.created, 'DD/MM/YYYY HH:MI:SS AM'), if(date_format(incidents.created, 'DAY')='Monday'&to_number(date_format(incidents.created, 'HH24'))IN(21,22,23), Concat(date_format(date_add(incidents.created, 1,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Tuesday'&to_number(date_format(incidents.created, 'HH24'))IN(9,10,11,12,13,14,15,16,17,18,19,20),date_format(incidents.created, 'DD/MM/YYYY HH:MI:SS AM'), if(date_format(incidents.created, 'DAY')='Tuesday'&to_number(date_format(incidents.created, 'HH24'))IN(21,22,23), Concat(date_format(date_add(incidents.created, 1,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Wednesday'&to_number(date_format(incidents.created, 'HH24'))IN(9,10,11,12,13,14,15,16,17,18,19,20),date_format(incidents.created, 'DD/MM/YYYY HH:MI:SS AM'), if(date_format(incidents.created, 'DAY')='Wednesday'&to_number(date_format(incidents.created, 'HH24'))IN(21,22,23), Concat(date_format(date_add(incidents.created, 1,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Thursday'&to_number(date_format(incidents.created, 'HH24'))IN(9,10,11,12,13,14,15,16,17,18,19,20),date_format(incidents.created, 'DD/MM/YYYY HH:MI:SS AM'), if(date_format(incidents.created, 'DAY')='Thursday'&to_number(date_format(incidents.created, 'HH24'))IN(21,22,23), Concat(date_format(date_add(incidents.created, 1,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Friday'&to_number(date_format(incidents.created, 'HH24'))IN(9,10,11,12,13,14,15,16,17,18,19,20),date_format(incidents.created, 'DD/MM/YYYY HH:MI:SS AM' ), if(date_format(incidents.created, 'DAY')='Friday'&to_number(date_format(incidents.created, 'HH24'))IN(21,22,23), Concat(date_format(date_add(incidents.created, 3,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Saturday',Concat(date_format(date_add(incidents.created, 2,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), if(date_format(incidents.created, 'DAY')='Sunday',Concat(date_format(date_add(incidents.created, 1,DAYS , 1), 'DD/MM/YYYY'),' 09:00:00 AM'), Concat(date_format(incidents.created, 'DD/MM/YYYY'),' 09:00:00 AM')))))))))))))

      Don't know if this will help, but we have spent a bunch of time creating stuff like this as well.