3 Replies Latest reply on Sep 28, 2018 7:21 AM by an.dy

    Calculated field - use the latest employee grade

    an.dy

      Hi all!

       

      I have a gantt chart for staff planning.

       

      In case the employee's grade was changed - it shows 2 separate rows.

      Here, employee 'Name 4' received new grade (old - Grade 1, new - GradeNEW). And in project 'Name' we can see 2 rows:

      pic1.JPG

      I need to see only new grades for all employees.

      So I need a calculated field.

       

      In Excel source data, 2 columns were changed for employee since August 2018:

      pic2.JPG

      The lower the LEVEL... column - the higher the Grade.

       

      Can you help me to make a calculated field Grade2 which will show only the latest grade of each employees regardless the current date / project etc.

        • 1. Re: Calculated field - use the latest employee grade
          Ritesh Bisht

          Hi Andy,

           

          Screen Shot 2018-09-28 at 1.12.37 PM.png

           

           

           

          Screen Shot 2018-09-28 at 1.13.15 PM.png

           

           

           

          OR GO WITH THE LEVELS

           

          Screen Shot 2018-09-28 at 1.14.39 PM.png

           

           

           

           

          Screen Shot 2018-09-28 at 1.14.51 PM.png

           

           

          This way you can DISPLAY.

           

          Please mark the answer as CORRECT & HELPFUL if it has really helped you

           

          Ritesh

          • 2. Re: Calculated field - use the latest employee grade
            an.dy

            I like the idea with MAX(Grade) but it does not work for all grades,

            for example if the employee's grade was 'Intern' and a new grade became "Grade1" - it does not work because I > G

            another example - grade was "Grade2" and a new grade became "Assistant" - it does not work because G > A

             

            is it possible to get via Calculated Field not the MAX of Grade column (which is textfield)  but the Grade value with the newest Date column (MAX of date column value) or the lowest LEVEL (min of LEVEL) for each Employee?

             

            I don't want to show Level column on a worksheet.

            • 3. Re: Calculated field - use the latest employee grade
              an.dy

              I have found the solution:

               

              Make first calculated field: Start all grades with "1-", "2-", "3-" with increase of seniority, and then use second calculated field to use only the MAX.

               

              But it is a workaround and will not work in case employee was demoted.

              Any ideas how to find the Grade of the MAX (date)

              Like:

              LastGrade = {FIXED [EMPID], MAX([START]): [Grade]}