1 Reply Latest reply on Sep 21, 2017 6:33 PM by swaroop.gantela

    How to create a filter by comparing multiple rows?

    Gary Chan

      I have an employee table with some employees having multiple records because they were re-hired.  See below a simple example.

       

      NameEmployee IDStatusHire DateTerminated Date
      Ada212Terminated3/1/20115/1/2014
      Ada331Active5/2/2014
      Bert310Terminated4/1/20167/1/2017
      Charles100Terminated6/1/20099/1/2009
      Charles120Terminated10/1/20103/1/2011
      Charles220Active7/1/2016

       

      Create an active employee list is simple because I can just filter Status = Active.  However, creating a terminated employee list is not so simple because I cannot simply filter Status = Terminated.  This will pick up Ada and Charles who have been re-hired and are active employees.  How do I create a filter that looks across multiple rows?

        • 1. Re: How to create a filter by comparing multiple rows?
          swaroop.gantela

          Gary,

           

          To carry information over multiple rows, you can use level of detail (LOD) calculations.

          Overview: Level of Detail Expressions

           

          Please see if the attached could be a first step.

           

          The first calculation uses the LOD method to fix an Employee's Max Hire Date to

          all rows for that have that Employee's name:

          [MaxHireDate]:

           

          { FIXED [Name]:MAX([Hire Date])}

           

           

          Then the second calculation determines the status by checking against the [MaxHireDate]

          IF [Hire Date]=[MaxHireDate] AND[Status]="Active" THEN "Active"

          ELSEIF [Hire Date]=[MaxHireDate] AND [Status]="Terminated" THEN "Terminated"

          END

           

          If you wanted this Status to carry over to all lines that have that Employee's name, you could use

          { FIXED Name:MAX(

          IF [Hire Date]=[MaxHireDate] AND[Status]="Active" THEN "Active"

          ELSEIF [Hire Date]=[MaxHireDate] AND [Status]="Terminated" THEN "Terminated"

          END

          )}