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



      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:




           RecruitmentYearMonth, //e.g. 201401




           Autonumber(RecruitmentYearMonth)     as RecruitmentCounter



           SalesYearMonth, // e.g. 201401


           [Actual Sales]



           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?