Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to calculate the last sunday of month March and Last sunday of Month October

AyubKhan
5 - Atom

Hope you all doing well. I am new to Alteryx designer.

 

I am stuck while designing the workflow where the requirement is I want to calculate the date of last Sunday of the month of March and October irrespective of the year. 

 

For Example, For the year 2020

March: 29-03-2020 (Sunday)

October: 25-10-2020 (Sunday)

 

For Example, For the Year 2021

March: 28-03-2020 (Sunday)

October: 31-10-2020 (Sunday)

 

So you can see data is changing, I want to calculate the date dynamically.

 

Thanks,

Ayub Khan

7 REPLIES 7
tsturge
7 - Meteor

Hopefully this gives you an idea and starting point, I am sure there are other ways but this is what we do without using a 2nd helper table.

 

tsturge_0-1606760512843.png

 

atcodedog05
20 - Arcturus

Hi @AyubKhan 

 

Here is a formula for the task. I am just taking the weekday of first of next month and adjusting the dates

DateTimeAdd(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month"),
-iif(ToNumber(
DateTimeFormat(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month")
,"%w"))=0,7,
-ToNumber(
DateTimeFormat(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month")
,"%w"))),
"days")

Output:

atcodedog05_1-1606762488134.png

Workflow:

atcodedog05_0-1606762394987.png

Hope this helps 🙂 Feel to ask if you have any questions


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Qiu
17 - Castor

@AyubKhan 
How about a more straightforward way/

1201-AyubKhan.PNG

AyubKhan
5 - Atom

Hi @atcodedog5

 

Thanks for the response.

 

One question here if I want to run this workflow dynamically. For example

 

when it runs for 2020, it should only give me the output date for the last Sunday of March and the Last Sunday of October for 2020

 

when 2021 starts the from 1st fo Jan it should give me the output date for the last Sunday of March and Last Sunday of October for 2021.

 

I do not want to hard code the input values.

 

Thanks,

 

atcodedog05
20 - Arcturus

Hi @AyubKhan 

 

Sure thats possible. You can give the month and get year from todays date

 

Here is formula to get the current year.

 

DateTimeYear(DateTimeToday())

 

atcodedog05_0-1606805338118.png

I am just giving month its picking up the year.

 

Output:

atcodedog05_0-1606805422833.png

 

Hope this helps 🙂 Feel to ask if you have any questions


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

atcodedog05
20 - Arcturus

Happy to help 🙂 @AyubKhan 

 

Cheers and Happy Analyzing 😀

 

Feel free to reach out if you face any issues 🙂

Qiu
17 - Castor

@AyubKhan 

If you like it mark it as accept please😂
multiple accept is allowed.

Labels