4 Replies Latest reply: Mar 15, 2016 11:02 AM by Kyle Luckow RSS

    Creating Tenure Filter by Sales Rep

    Kyle Luckow

      Hello, I would like to create field in the script that shows how long the Sales Rep has been active.  I'd like to be able to pull it in as a filter called Tenure that has options in increments of half years 0.5, 1, 1.5 etc.   Allowing an individual to select sales reps that have been active for a certain amount of time easily.

       

      Is there a way to calculate a number for each sales rep that takes todays date less the date of their first appointment and round it to the half year?

       

      My script has the following data in it that I believe would be necessary.

       

      Appointments:

       

      LOAD  DISTINCT

          AppointmentId,

          AppointmentDate,

          SalesRepName,

       

       

      Thanks!

       

      Kyle

        • Re: Creating Tenure Filter by Sales Rep
          Andrew Mein

          TODAY() - AppointmentDate will give you the number of days

           

          then the CLASS() function can be used to group them

          Buckets

            • Re: Creating Tenure Filter by Sales Rep
              Kyle Luckow

              Thanks for the response Andrew but I need it to be the minimum AppointmentDate specific to a SalesRepName.  Any suggestions?

                • Re: Creating Tenure Filter by Sales Rep
                  Andrew Mein

                  [SalesRepDates]:

                  LOAD

                       SalesRepName,

                       FirstAppointmentDate,

                       CLASS(FLOOR(TODAY() - FirstAppointmentDate)/365,0.5) AS DaysSinceFirstAppointmentGroups;

                  LOAD

                       SalesRepName,

                       MIN(AppointmentDate) AS FirstAppointmentDate

                  FROM <YourDataSource>

                  GROUP BY

                       SalesRepName;

                   

                  Afraid it assumes a 365 day year in every case.

                  FLOOR will remove the time fraction from your date - just in case its in there

                  CLASS groups the result into 0.5 increments.

                    • Re: Creating Tenure Filter by Sales Rep
                      Kyle Luckow

                      Thanks Andrew! I am still missing something I am assuming is really easy.  I keep getting an invalid path error when I put this in.  Any idea what could cause that?  I have that EnabledDB_Reporting source in multiple locations in my script already.

                       

                      [SalesRepDates]:

                      LOAD

                           SalesRepName,

                           FirstAppointmentDate,

                           CLASS(FLOOR(TODAY() - FirstAppointmentDate)/365,0.5) AS DaysSinceFirstAppointmentGroups;

                      LOAD

                           SalesRepName,

                           MIN(AppointmentDate) AS FirstAppointmentDate

                      FROM "EnabledDB_Reporting".Reporting.Appointments

                      GROUP BY

                           SalesRepName;