Compare with previous row date and identify min or max

[Siraj]

Hi, 

I have below scenario. please provide the solution.

Need to identify the project number which phase date is greater than previous phase date.

In the below example third phase date is less than second phase, So my condition result would be pick the project number  A-1234.

Project Number	Phase	Phase Date (MM/DD/YYYY)
A-1234	First Phase	04/18/2019
A-1234	Second Phase	05/25/2019
A-1234	Third Phase	02/21/2019
A-1234	Fourth Phase	08/22/2019
B-5678	First Phase	01/25/2019
B-5678	Second Phase	02/22/2019
B-5678	Third Phase	03/28/2019
B-5678	Fourth Phase	05/30/2019

Thanks in Advance.

Sirajahamed S

[StarinieriG · Accepted solution]

Hi,

if you could solve that inside the script, try with something like this (before of that, you have to order your data):

If([Project Number]=Previous([Project Number]), If([Phase Date (MM/DD/YYYY)]<Previous([Phase Date (MM/DD/YYYY)]),[Project Number]))

[Siraj]

Hi

Thanks for your reply. I will try this and let you know.

[Siraj]

Hi,

I am not getting the result. Below is my script

TempLogicalSequence:
load
ProjectNumber,
Proj_Del_Order as Phase_ORDER,
if(ProjectNumber=previous(ProjectNumber),
if(date(DelDue,'MM/DD/YYYY')<previous(date(DelDue,'MM/DD/YYYY')),1,0)) as Due_Date_Sequence
resident HighlightProjectDel

order by Phase_ORDER asc, DelDue asc;

Could you provide any solution

Thanks in advance

Sirajahamed S

[sunny_talwar · Accepted solution]

Try this

TempLogicalSequence:
LOAD ProjectNumber,
     Proj_Del_Order as Phase_ORDER,
     If(ProjectNumber = Previous(ProjectNumber),
          If(DelDue < Previous(DelDue), 1, 0)
     ) as Due_Date_Sequence
Resident HighlightProjectDel
Order By ProjectNumber, Phase_ORDER, DelDue;

[StarinieriG · Accepted solution]

You have not to order by date to use that expression. Only by ProjectNumber

[Siraj]

Hi 

Thanks to both. It was working fine  by removing the date in order by .