1 Reply Latest reply on Sep 23, 2016 11:34 AM by diego.medrano

    Need to find the count of reps and no. of times deviation from the Tour planner with reps Actual report.

    Naveen Prashanth

      Hi all,

       

      I have a problem to solve, were i need to take the count of reps who have deviated and count the no. of times category is been deviated from there tour plan. eg reps who have planned to visit a category but in actual they have visited a different category.  The data is in the format of  two sheets were I've made an left join.

       

      Cases :-  The main objective is to find Deviated reps and non deviated reps with no of times they are deviated.

       

      Case - 1. If Rep Naveen as planned a category (HQ) in TP for 1-Aug-2016 and in actual report entered (Ex-HQ) in 1-Aug-2016 then that day is deviated , were he would have met 10 customers, but it should be counted as 1 deviation.

       

      Case - 2. If Rep Naveen as planned a category (HQ) and Flag as 'attendance' for 2-Aug-2016 in TP, and in actual report have done two task were he has Category (HQ) and  Flag(Attendance) for the first task and then have entered Category (Ex-HQ) and Flag  (Field)  for the second task then it is not deviated.

       

       

      I tried solving it by using Equal to function and count users by countd(reps) and count the no.of times by count(date), But it didn't work. Could someone please help me solve the problem.

       

      The answer that i was expecting looks something like this, and i would like to drill down to view single users deviated count too.

       

      Category
      No of users deviatedNo of times deviated
      category37

       

      The data set is attached below, please do find it.

       

       

      Thanks,

      Naveen