Hi, I am stuck on work out sales productivity - I have a user table with start and end dates and an opportunity table with close dates. I would like to be able to work out for each period the number of users who's start and end dates include the close date.. here is an extract from my loadscript..
let vDate = year(Date(Now(), 'YYYYMMDD')) & '-' & num(month(Date(Now(), 'YYYYMMDD')));
Opportunity:
LOAD
OpportunityId,
OpportunityName,
StageName,
Value,
ClosedDate,
year(CloseDate) & '-' & num(month(ClosedDate)) as CloseDateYM
FROM [lib://Opportunity.qvd](qvd) WHERE [Stage Name] = 'Closed Won';
User:
LOAD
OwnerId as UsersId,
UserName,
Region,
if(isnull(EndDate), '$(vDate)',year(EndDate) & '-' & num(month(EndDate))) as EndDate,
year(StartDate) & '-' & num(month(StartDate])) as StartDate
FROM [lib://User.qvd](qvd);
Ideally I would then like to be able select Region or any other field I am bringing into the model. The goal is to then be able to divide the sum of Value by the number of heads to arrive at a sales productivity number for a period. Then be able to aggregate that up from Months to quarters/years etc..
I am sure this must be a common problem - can anyone advise me how to go about this.
