2 Replies Latest reply on Sep 15, 2016 8:03 AM by youthesh sagar

    Need help in writing a calculated field.

    youthesh sagar

      Below is the sample data set.(I have more than 100,000 records).

       

      I want to write a calculated filed to get the number of employees who have changed department monthly.

       

      In below example emp id 111 and 121 have changed departments.

       

      I want a graph with month in row self and the count of employees who have changed their departments in column self or vise versa.

       

            

      Emp IDEmp Name Joining DateDepartmentStart DateEnd Date
      111john05/05/2016finance05 May 201607 May 2016
      121sam06/05/2016IT06 May 201629 May 2016
      131tom07/05/2016IT07 May 201601 January 2100
      141tim08/05/2016HR08 May 201601 January 2100
      111john09/05/2016Accounts08 May 201601 January 2100
      121sam06/05/2016IS30 May 201601 January 2100

       

       

      Please help.Thank you

        • 1. Re: Need help in writing a calculated field.
          chris.moore.11

          Hi, it sort of depends on the conditions, and definitions you have in mind for changing departments.There are a few ways that this could be done.

           

          If you base it off of only "start date" you could create a field that would flag if its not their first start date.

           

          if [Start Date] = { FIXED [Emp ID] : MIN([Start Date]) } then 0 else 1 end

           

          Capture1.PNG

          1 of 1 people found this helpful
          • 2. Re: Need help in writing a calculated field.
            youthesh sagar

            chris.moore.11

             

            what if there is are additional column (Job role) which is also being tracked for each employee.There is one more column called active_flag ("Y" or "N") which shows which record is a active record.

            i.e, start and end date gets updated when job role changes and active flag are set to "N" fro old records and Latest record will have active flag as "Y".

             

            In this case i want to track only employees who change their department.

            Will the above calculated field work?

             

            Can you please let me know who to handle this scenario.

             

            Thank You