derive flag using today to help flag current or previous

[Raj_007]

Hi All,

Thanks for your time. I have this excel file and need to derive the flag based on the today/system date so that i can flag current or previous or future
i need to use today and look at the RStartDate and RStartEndDate...something like below

If the today is between RStartDate and REndDate then current - but how do i flag the previous1 (the one before the current )

[gabrielvilella · Accepted solution]

Those conversion errors are expected and you should not worry about them. 

[Raj_007]

Gabriel, Thank you so much. I really appreciate your time. without the join we can also directly union the true and false records together correct?

This is what i have been looking for..          :)

[gabrielvilella]

You need the join so the order of the numbers is backwards. If you union, you will reorder all other fields. Happy to help @Raj_007 !

[Raj_007]

Hi Gabriel,  need some help with the logic that we used to derive the current vs prior - the prior numbering is not getting the correct result for some reason it is not flagging the prior release 1 in each release type 

that is for release type CR i need prior release 1, prior release 2 etc and for release type MR i need prior release1, prior release2 etc - but for some reason it is only flagging prior release 1 in MR only...

i have attached the excel file with yellow color on where it is getting wrong

[Raj_007]

sample

[gabrielvilella · Accepted solution]

Hi @Raj_007, please see this updated version. 

[Raj_007]

Hi Gabriel, thank you so much, this is really very helpful. as always really appreciate your time