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?
