1 Reply Latest reply: Aug 22, 2016 10:35 AM by Sunny Talwar RSS

    Calculate the Number of Staff Employed in A Period ?

    ian turner

      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.