Generate Row calculation issue

[swapsingh2712]

Hello all,

I've created the workflow for month generation based on the dates. But there's one issue with the row generation.

So for TI000 the activation date is 2022-03-01 and it's  quarterly, but it's generating the row where  2022-01-01, which is incorrect because it should not generate the month which is less than the activation date.

For other test, it works fine, 

Could you please help me fix this ?

I hope a small logic change could help because writing a new logic again would take a lot of time and we have large datasets.

Workflow and snip attached.

Regards,

Swapsingh

[mceleavey]

Hi @swapsingh2712 ,

The problem is in the logic in the IF statement:

IF datetimeadd([recent_testing_month],-3,"Months")<datetimeadd([Current Date],-6,"Months")
THEN
MAX(MAX([recent_testing_month],datetimeadd([Current Date],-6,"Months")),MIN(datetimeadd([recent_testing_month],-3,"Months"),datetimeadd([Current Date],-6,"Months")))
ELSE
MIN(MAX([recent_testing_month],datetimeadd([Current Date],-6,"Months")),MAX(datetimeadd([recent_testing_month],-3,"Months"),datetimeadd([Current Date],-6,"Months")))
ENDIF

If 3 months prior to the recent_testing_month is older than 6 months, then first clause. This is NOT the case for that row, as the recent_testing_month value is 2022-04-01, so the second clause is triggered.

If you place the second clause into a formula tool as a stand alone formula, you get 2022-01-01:

So your second clause is wrong, if that's not what you're trying to achieve.

If you remove the MIN clause, you get 2022-04-01:

M.

[swapsingh2712]

Thank you so much @mceleavey for your quick response. Actually If I remove the second clause, it might affect other values.

Is it possible if we can add any formula that should hide/remove the rows if the month is less the activation date, you know hard code it instead of changing the entire logic?

Swapsingh2712

[mceleavey]

@swapsingh2712 ,

yes, you can do whatever you want.

You just need to change your logic, as you currently have only two clauses, if not one then the other, but you need another clause to capture this.

If you want to simply remove the rows, then use a filter tool after the Generate Rows tool to remove those where the month is before the activation date.

M.

[swapsingh2712]

@mceleavey oh okay.

would you please help me with the logic change or adding the clause, as I tried changing it but it's not giving me the desired output.

Swapsingh2712

[mceleavey · Accepted solution]

@swapsingh2712 ,

I'm not really sure what you're asking, but if you simply want to remove those rows I've added a filter at the end. I don't know what you're trying to do, so I can't add in logic as you will need to do that.

The filter simply removes those records where Month < Activation Date.

M.