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

# How to create a filter by comparing multiple rows?

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
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?

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

)}