1 Reply Latest reply on Nov 21, 2017 10:02 AM by Tom W

    Count of workers who are lapsed from last 1 year(in B/w2016-2017) and working in the current year 2017.

    Nishant Dwivedi

      Hi All.

      I have one requirement in which i have to satisfy 3 scenarios below.

       

      Below is how my data looks like,  Please Note that the Same worker can work many times according to their contracts which can last from 1 week up to a year also.

       

      I have to satisfy all the 3 scenarios below.

       

      1) Who have not been working with us for the last year that is between 2016-2017?

       

      - if  YEAR([Contract Start Time]) = 2016 then 'out' else 'in' END

      (Have Taken Only the workers that are IN As we don't want the workers who have worked between 2016 to 2017)

       

      2)  But have been working before last year.

      NOT SURE HOW TO DO THIS

       

      3) Who have been paid  in the year 2017?

       

      - Can directly take STATUS field in filter and make it PAID

      And

      -Also put  Year(Contract Start Time) = 2017 in Filters

       

       

       

      Worker IdContract Start TimeContractEnd TimeStatus
      WOR-1114419/04/201719/04/2017Paid
      WOR-1114420/04/201720/04/2017paid
      WOR-1114417/06/201617/06/2016Paid
      WOR-1114420/06/201620/06/2016not paid
      WOR-1114419/04/201719/04/2017Paid
      WOR-1114420/04/201720/04/2017Paid
      WOR-1114423/06/201623/06/2016paid
      WOR-1114424/06/201624/06/2016Paid
      WOR-1114727/06/201627/06/2016not paid
      WOR-1114728/06/201628/06/2016Paid
      WOR-1114729/06/201629/06/2016Paid
      WOR-1114719/04/201719/04/2017paid
      WOR-1114720/04/201720/04/2017Paid
      WOR-1114702/11/201602/11/2016not paid
      WOR-1114719/04/201719/04/2017Paid
      WOR-1114720/04/201720/04/2017Paid
      WOR-1114713/06/201613/06/2016paid
      WOR-1114714/06/201614/06/2016Paid
      WOR-1234215/06/201615/06/2016not paid
      WOR-1234219/04/201719/04/2017Paid
      WOR-1234220/04/201720/04/2017Paid
      WOR-1234220/06/201620/06/2016paid
      WOR-1234219/04/201719/04/2017Paid
      WOR-1234220/04/201720/04/2017not paid
      WOR-1234223/06/201623/06/2016Paid
      WOR-1234224/06/201624/06/2016Paid
      WOR-1234219/04/201719/04/2017paid
      WOR-1234220/04/201720/04/2017Paid
      WOR-1234204/05/201604/05/2016not paid
      WOR-1234205/05/201605/05/2016Paid