0 Replies Latest reply: Nov 2, 2016 1:44 AM by Richard Blakemore RSS

    Calculate Salesperson's first 6 months' sales - no matter when they start

    Richard Blakemore

      Hi

       

      I have a sales organisation that wants to track the first 6 month's worth of sales for each sales person - no matter when they start. I would like to do this in set analysis rather than summing up in the script as this organisation has literally tens of thousands of sales people...

       

      The output I am looking for is a straight or pivot table as follows:

       

      Sales                                         Month 1 Month 2 Month 3 Month 4 Month 5 Month 6

      Sales Person      Recruit Month

      Salesperson1          201601

      Salesperson2          201603

       

      At this point, the simplified version of the data is as follows:

       

      SalesPersons:

           SalespersonID,

           RecruitmentYearMonth, //e.g. 201401

       

      RecruitmentCalendar:

           RecruitmentYearMonth,

           Autonumber(RecruitmentYearMonth)     as RecruitmentCounter

       

      Sales:

           SalesYearMonth, // e.g. 201401

           SalespersonID,

           [Actual Sales]


      Calendar

           SalesYearMonth,

           Autonumber(SalesYearMonth)          as     SalesYearMonthCounter

       

      I have been trying all manner of set analyses e.g.

      =Sum({1<SalesYearMonthCounter={">=$(=RecruitmentCounter)<=$(=RecruitmentCounter+6)"}>}[Actual Sales])

       

      However, it only seems to get me an answer if only 1 recruitment month is chosen, and nothing if more than one or no selections in recruitment month / Recruitment Counter are made.

       

      Any ideas?