new column generate logic based on dates

Question

Hello All,

I've created a alteryx workflow which is generating rows (Month column) based on the activation and decommission dates mentioned in the datasets.

for Month column generation our logic is "It should generate the rows that comes within the range 6 months backward & 5 months forward from the current date (2/17/2021)"

Now we have a column named as Evaluation frequency which contain values like Annual, semi annual, quarterly, monthly and there's another column and "recent_testing_month". If the test is other then "Monthly" then that's how the logic should work
For example we've a test named as TI1059 and it's recent testing month is "11/30/2021 18:00" and evaluation frequency is "Quarterly".The month column should generate the dates within the range (-6 and +5) from current month and being a quarterly test it should be 3 months apart. So it should generate the below dates

2022-02-28(yyyy-mm-dd)
2022-05-28
2021-11-30
2021-08-30

we've a test named as TI1941 and it's recent testing month is "8/31/2021 19:00 " and evaluation frequency is "Semi-Annual".The month column should generate the dates within the range (-6 and +5) from current month and being a semi annual test it should be 6 months apart. So it should generate the below dates

2021-08-31
2022-02-28

we've a test named as TI1555 and it's recent testing month is "5/31/2021 19:00" and evaluation frequency is "Annual".The month column should generate the dates within the range (-6 and +5) from current month and being a annual test it should be 12 months apart. So it should generate the below dates
2021-05-31
2022-05-31

also if all these tests have decommission date for example if the test named TI1026 which is Quarterly and has "recent_testing_month" as "9/30/2021 19:00" and has decommission date as "11/8/2021 18:00". The month column should generate the dates within the range (-6 and +5) which is
2021-06-30

2021-09-30

but for some reason my logic ( for month generation) is not working for Annual, semi annual, quarterly. The date looks incorrect

Please help me

Test_Date_Generate update_2.yxmd

Workload Discussion.xlsx

workload Annual.xlsx

swapsingh2712 · Answer

@binu_acs Yeah actually my month generation logic is not working for few of the tests.

also the below issue

For TI843, the recent testing month is "12/31/2021 6:00:00 PM" and evaluation frequency is "Quarterly", This test should also generate the row with the date "09/31/2021" as it comes  under the date range +6 months and -5 months from the current date.

Somehow the workflow is not backtracking the dates before "recent_testing_month"

Can you suggest any logic change to my month generation formula ((Max([recent_testing_month],datetimeadd([Current Date],-6,"Months")))) in order to fix these errors for annual , semi-annual and quarterly?

BR

Swapsingh2712

binu_acs · Answer

@swapsingh2712 I think your initiatlize expression (Max([recent_testing_month],datetimeadd([Current Date],-6,"Months"))) in the generate rows derive the value as 8/22/2021

value of month 8/22/2021

First Iteration - Month less than   datetimeadd([Current Date],6,"months") (2022-08-22)

month updated to 2022-08-22 because of the condition datetimeadd([Month],12,"months")

Second iteration -  Month equal to    datetimeadd([Current Date],6,"months") (2022-08-22)

that's why you are seeing two rows for the test id TI1555

please let me know if I missing something

swapsingh2712 · Answer

Hello @binu_acs

Thank you so much for your response.

I ran the workflow and looks like the data still shows incorrect. I've listed few of the test_id in the attached excel.

For TI1555, the recent testing month is 5/31/2021 7:00:00 PM and evaluation frequency is "annual" so the month row should generate 1 row with date 5/22/2022 (as it comes under the date range +6 months and -5 months from the current date), but currently it's showing 8/22/2021 which is incorrect

Same issue is TI712

For TI843, the recent testing month is "12/31/2021 6:00:00 PM" and evaluation frequency is "Quarterly", This test should also generate the row with the date "09/31/2021" as it comes  under the date range +6 months and -5 months from the current date.

Somehow the workflow is not backtracking the dates before "recent_testing_month"

Here we are only concerned with the month calculation.

The workflow which I created also depicting the same behavior. Could you please let me know if any logic needs to be updated.

BR

Swapsingh2712

Error.xlsx