Calculate Anniversary Dates

[Uthpala_K]

Hi Team,

I am trying to calculate each occurrence of an anniversary date between a start and an end date that has an annual anniversary date.

I used the Generate Rows tool, however, am not getting the correct dates as it doesn't generate the last row for me.

e.g. 1

Start Date - 2017-05-08

End Date - 2030-08-04

Annual Anniversary Date - 11/08 (i.e. first anniversary for this item will be on 2017-08-11)

e.g. 2

Start Date - 2016-09-19

End Date - 2031-09-18

Annual Anniversary Date - 10/08 (i.e. first anniversary for this item will be on 2016-10-08)

How do I arrive at the calculation table using Alteryx?

[grazitti_sapna]

Hi @Uthpala_K , for eg 1

Start Date - 2017-05-08

End Date - 2030-08-04

Annual Anniversary Date - 11/08 (i.e. first anniversary for this item will be on 2017-08-11)

As your end date is 2030-08-04 which is less than 11-08 it will show you result till year 2029 unlike in case of eg 2 you have used year 2031, in case you want to display result till year 2030 of your eg 1 you need to change the end date to 2031 as alteryx will not support the date outside the mentioned date range.

I hope this clarifies your issue.

Thanks.

[Jean-Balteryx]

Hi @Uthpala_K ,

I don't get what's wrong. What are you expecting ?

Cheers,

Jean-Baptiste

[grazitti_sapna]

@Uthpala_K , I have mocked up a workflow please refer to it.

Thanks.

[atcodedog05 · Accepted solution]

Hi @Uthpala_K ,

As per the description i think you said the last date was not correct dates since it doesn't generate the last row

Like below.

Here is the modified workflow. I have changed the end date condition to end date + 1.

And formula is modified to change anniversary date when end date is before.

Hope this helps : )

[RolandSchubert]

Hi @Uthpala_K ,

I think, you have to modify the expression in the Generate Rows tool a bit to include the "End Date" in the table:

IF DateTimeAdd([Billing Anniversary Date] ,1, [Frequency]) > [End Date]
AND
DateTimeYear(DateTimeAdd([Billing Anniversary Date] ,1, [Frequency])) = DateTimeYear([End Date])
THEN
[End Date]
ELSE
DateTimeAdd([Billing Anniversary Date] ,1, [Frequency])
ENDIF

I've attached the modified workflow. Let me know if it works for you.

Best,

Roland

[Uthpala_K]

@atcodedog05 Thanks so much for this! I tried solving this issues in multiple different ways, but didn't once think of adding the 1 to the formula! Jeeze!

You are a star!

[Uthpala_K]

Thank you so much for your reply.

This still drops off the last year of the calculation.

The solution provided by @atcodedog05 sorts the issue 🙂

[Uthpala_K]

Hello, 

I am trying to calculate each occurrence of an anniversary date between a given start and an end date.

I was having trouble configuring the last year of occurrence.

The solution provided by @atcodedog05 sorts this issue.

[Uthpala_K]

Thank you so much for your reply.

This solution still drops off the last year of the calculation.

The solution provided by @atcodedog05 sorts the issue.