Trying to Get the Next Date

[colin_g]

I have a set of data for a data point that only has a begin date. I need to calculate the end date. For example,

Analysis_Name	BEGINDATE	ENDDATE	End_Date_2
Analysis 1	2015-01-01	2018-01-01	2018-01-01
Analysis 1	2018-01-01	2021-01-01	2018-01-01
Analysis 1	2021-01-01	2022-01-01	2018-01-01
Analysis 1	2022-01-01	2078-01-01	2018-01-01

Currently i'm trying to use the OVER functions

Max([BEGINDATE]) over (Next([ANALYSIS_NAME]))

I would like Enddate to be what is in the ENDDATE column but I'm instead getting what is in the End_Date_2 column

I can do this easily in SQL but this is an excel file that i'm reading in

[Olivier Keugue Tadaa]

Hi colin_g

Since the (Next([ANALYSIS_NAME])) is the next different analysis and NOT the next line or record, here is what I would suggest :

1- calculate a column named "Key" like this:: [Analysis_Name] & [BEGINDATE]

2- create the "end_date" like this : Max([BEGINDATE]) OVER (Next([Key]))
This ensures that a new end date will be calculated when you change the analysis and a BEGINDATE (the Key)
However, note that the last line will have an empty end_date

I hope this helps

[colin_g]

Thanks Olivier! That almost worked. I actually want a NULL for the last line (I could then substitute a 2078 date) but didn't get one

What did end up working was creating an [Analysis_Number] for each Analysis:

Count([Analysis_Name] ) over (intersect([Analysis_Name] , AllPrevious([BEGINDATE])))

 

and then getting my Enddate from that

SN(Max([BEGINDATE]) over (Intersect([Analysis_Name], Next([Analysis_Number]))), Date(2078,1,1))

 

 

Kind of a long way to go to get around it but it works

 

 

 

 

[Olivier Keugue Tadaa]

Very happy to hear that it worked 

However, the expression SN(Max([BEGINDATE]) OVER (Next([Key])),Date(2078,1,1)) also returns the expected result (at least from what I understood)

See below

 

 

[colin_g]

I get this;

ANALYSIS_NAME	BEGINDATE	Analysis_Number	End_Date_Good	Key	Enddate_key
6105	2015-01-01	1	2018-01-01	61052015-01-01	2018-01-01
6105	2018-01-01	2	2021-01-01	61052018-01-01	2021-01-01
6105	2021-01-01	3	2022-01-01	61052021-01-01	2022-01-01
6105	2022-01-01	4	2078-01-01	61052022-01-01	2018-01-01

I think it's grabbing the next analysis because if i put a filter on 6105 before I calculate the column, it's fine. However, if i put the filter on after, i get the bad last date

[Olivier Keugue Tadaa]

Yes, that makes sense if you filter ... 

By the way, I am just curious to know how you would have done this with SQL 🙏

Edited September 19 by Olivier Keugue Tadaa

[colin_g]

I would use LEAD and PARTITION BY. Something like this:

select e.*, trunc(e.begindate, 'MON') begindate ,

nvl(lead(trunc(e.begindate, 'MON')) over (partition by e.analysis_name order by e.begindate), TO_DATE('01-Jan-2078', 'DD-MON-YYYY')) as enddate

from analysis e

[Olivier Keugue Tadaa]

👌