Generating rows for different dates

Hello all,

I am currently working on generating rows for different anniversary dates from a specific period. For example, current study period is 2016/01/01 to 2016/10/01, and two rows should be created for a policy entered in 1995/10/01, which are 2015/09/30 and 2016/09/30.

I have built up two similar workflows, from my understanding, their only difference is the condition expression in row generation function, and they should produce identical results, but they are not.

I'm really confused about what is going wrong. Could anyone help and suggest?

Thanks!

DateTime Generation.yxmd

Date Time

Common Use Cases

Accepted answers

DataNath

Hey @KKone I just wrapped the first part of the second Condition Expression in ToDate() and this seemed to remedy the issue.

As you're using DateTimeAdd, the output was 2016-10-01 00:00:00 - as the time part had been added, this couldn't be compared directly (with <=) to 2016-10-01 as they're not the same, as shown in the screenshot below:

The reason your first worked fine is because you were using datetimediff as the Condition check and so you can compare the difference on a daily level regardless of whether or not time is there in one and not the other.

All comments

DataNath

Hey @KKone I just wrapped the first part of the second Condition Expression in ToDate() and this seemed to remedy the issue.

KKone

Thanks! @DataNath

The ToDate() perfectly solve my problem! But I don't quite understand. Keeping the original expression, and I change the [Start_DT] into 2015/01/01. then two records will be generated, (2014-09-30 and 2015-09-30), does it mean the loop goes well until [End_Dt]? And still, can't see 2016-09-30 in output.

And one more question, will there be any way to know the default output format for different formulas?

DateTime Generation2.yxmd

output.JPG

DataNath

Hey @KKone sorry I missed this! I'm actually not 100% sure how this works but as you're using a DateTimeAdd function, this will add HH:MM:SS to the output and so when you're comparing this to a Date with no time element, Alteryx isn't able to distinguish whether it's before or after that point in the day. That's all I can think of as adding ToDate() to the DateTimeAdd works, as does adding ToDateTime() to the [End_Dt].

To find a list of functions you can look here: https://help.alteryx.com/20221/designer/functions - Within each link they usually show the output format you should expect.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703