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
At this point, the simplified version of the data is as follows:
RecruitmentYearMonth, //e.g. 201401
Autonumber(RecruitmentYearMonth) as RecruitmentCounter
SalesYearMonth, // e.g. 201401
Autonumber(SalesYearMonth) as SalesYearMonthCounter
I have been trying all manner of set analyses e.g.
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.