Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Find last Saturday of prior month

severhart
8 - Asteroid

Hi 🙂 I've tried searching but all I'm finding is results to get the last day of the prior month.

 

Situation: reporting weekly data. I have data stored in my file with a keyfield of Week Ending day. I've been able to get a formula to extract the prior week's date and the prior year's corresponding week's date, but I need to be able to get the last one of the prior month.

 

ie: today is 6/17/2020

 

 

 

datetimeadd(DateTimeAdd(DateTimeNow(),Switch(DateTimeFormat(DateTimeNow(),'%a'),0,'Sat',0,'Sun',-1,'Mon',-2,'Tue',-3,'Wed',-4,'Thu',-5,'Fri',-6),"days"),0,"days")

 

 

the above gets me 6/13/2020

 

 

 

DateTimeAdd(datetimeadd(datetimenow(),-364,"days"),Switch(DateTimeFormat(DateTimeNow(),'%a'),0,'Sat',0,'Sun',-1,'Mon',-2,'Tue',-3,'Wed',-4,'Thu',-5,'Fri',-6),"days")

 

 

the above gets me 6/15/2019

 

 

I'm seeking a formula that will give me 5/30/2020 (the last Saturday of the prior month) and I'm stuck. Can anyone help me get there?

2 REPLIES 2
ChrisTX
15 - Aurora

See if the attached workflow gives you the desired output.

 

 

Annotation 2020-06-17 125547.png

 

Chris

severhart
8 - Asteroid

Hi Chris - I think this will get me started. I was hoping for a single formula vs a group of tools to incorporate, but I think I can adapt this to get me going. I really appreciate you taking the time to share an example!

Labels