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]

The one you considered current release for type CR has a starting date of 03/11/2022 and current date is 03/04/2022, which is prior to that. You mentioned that to be considered current the current date should be in between start and end dates. Is this correct? 

[Raj_007]

Hi Gabriel

that is correct so i have 2 release types 1)CR  and 2)Monthly release - in 1 calendar year i will have 3 CRs and 7 MR's

based on today's date see if that is between the Release Start Date and Release End Date if Yes then flag as current for CR and for MR

something like Current_Release_CR, Current_Release_Monthly or MR
and also i need to flag the previous release as Previous1 and Previous2, then flag the future ones as Future1, Future2 Etc

thanks a lot

[Raj_007]

the reason i mentioned 3/11/2022 is because thats the day the release starts for CR122 which is current release for this year

If we use 03/04/2022 then the current will be CR321 based on the dates... we can try this one also

[gabrielvilella]

See if this is what you are looking for.

[Raj_007]

I got this error - i have alteryx 2020.4 - let me recreated that might help
Error: Formula (3): Parse Error at char(25): Internal error: AMP Engine does not yet support the GetFunctionAddInExecuter function. (Expression #1)

[gabrielvilella]

I need to do some corrections. The error you got is because you have an older Alteryx version. 

[Raj_007]

Hi Gabriel, The flag field in my excel file is just for example: We have current, previous, future flagged but how do i know which is previous1 ..the one before the current.. that's where i got stuck.. the one before the current should say something like Previous1 and the one before that Previous2
for the future ones.. the one after the current Future1 then the one after the Future1 should be Future2 etc... sorry if i confused you

[gabrielvilella]

I did some corrections, see this updated version now.

[Raj_007]

Hi Gabriel, do you see the conversion errors in multi -row formula? i see we have enough size for that flag column...
ConvError: Multi-Row Formula (8): TONUMBER: Current Monthly Release (CCBMR2202) lost information in conversion