Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

0 Replies