topic Compare with previous row date and identify min or max in App Development

Compare with previous row date and identify min or max

Siraj — Thu, 12 Sep 2019 13:15:19 GMT

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

Re: Compare with previous row date and identify min or max

StarinieriG — Thu, 12 Sep 2019 13:32:28 GMT

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]))

Re: Compare with previous row date and identify min or max

Siraj — Fri, 13 Sep 2019 07:02:23 GMT

Hi

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

Re: Compare with previous row date and identify min or max

Siraj — Fri, 13 Sep 2019 11:14:07 GMT

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

Re: Compare with previous row date and identify min or max

sunny_talwar — Fri, 13 Sep 2019 11:21:15 GMT

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;

Re: Compare with previous row date and identify min or max

StarinieriG — Fri, 13 Sep 2019 12:04:14 GMT

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

Re: Compare with previous row date and identify min or max

Siraj — Fri, 13 Sep 2019 12:24:20 GMT

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