Get previous date using OVER function

[Chifuku Mita]

I'm trying to get a previous date and insert it as a calculated column [PreviousDate] using following table.

 

 

 

 

ID

 

 

Date

 

 

PreviousDate

 

 

 

 

111

 

 

2011/11/01

 

 

 

 

 

 

 

111

 

 

2011/12/01

 

 

2011/11/01

 

 

 

 

111

 

 

2012/01/01

 

 

2011/12/01

 

 

 

 

222

 

 

2011/11/01

 

 

 

 

 

 

 

222

 

 

2012/02/01

 

 

 

 

 

 

 

222

 

 

2012/03/01

 

 

2012/02/01

 

 

 

 

I've tried the following custom expression, but it seems to calculate a value only for cases where there is a consecutive month (i.e. Row where ID=222 and Date=2012/02/01, PreviousDate is not calculated).

Max([Date]) OVER (Intersect([iD],Previous([Date])))

[Manoj Singh]

1) Create a new calculated column with the expression:RowId()

2) Create another calculated column with the expression:First([Date]) OVER (Previous([column created in 1st step]))

Below is the output:

ID Date Final RowID

111 11/1/2011 1

111 12/1/2011 11/1/2011 2

111 1/1/2012 12/1/2011 3

222 11/1/2011 1/1/2012 4

222 2/1/2012 11/1/2011 5

222 3/1/2012 2/1/2012 6

[Chifuku Mita]

Thank you for your quick response.

 

The reason why I used Max([Date]) OVER (Intersect([iD],Previous([Date]))) is that I'd like to get PreviousDate by ID such as 111 or 222.

 

Should I assign Rowid() by ID

 

 

[Manoj Singh]

Can you share the expected result Also little more explanation about your requirement.

[Chifuku Mita]

Thank you. The expected result is as below.

 

ID    Date          PreviousDate   

111    11/1/2011                       

111    12/1/2011    11/1/2011    

111    1/1/2012    12/1/2011    

222    11/1/2011      

222    2/1/2012    11/1/2011    

222    3/1/2012    2/1/2012    

 

 

 

ID 111 has 3 rows and in the first row won't have PreviousDate as you know. ID 222 also has 3 rows and the first row of ID 222 (i.e. 4th row in total) won't have PreviousDate. Make sense

 

 

[Shashank Gurnalkar]

Yes, for the DATE column, it seems to calculate a value only for cases where there is a consecutive month. As a workaround, can you try to use a different column in the Previous function as mentioned in the following steps:

Create a calculated column as [Rank] with the following expression:

Rank([Date],"Asc",[iD])

Create another calculated column as [PreviousDate] with the following expression:

First([Date]) OVER (Intersect([iD],Previous([Rank])))Following is the output:

[Chifuku Mita]

Thank you.