derive flag using today to help flag current or previous

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 )

CR and MR Release Info.xlsx

Developer Tools

Accepted answers

gabrielvilella

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

gabrielvilella

Hi @Raj_007, please see this updated version.

Flag.yxmd

All comments

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.

Flag.yxmd

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.

Flag.yxmd

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

gabrielvilella

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, thank you so much, this is really very helpful. as always really appreciate your time

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335