Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Week Start formula?

robl93
6 - Meteoroid

Hi all,

 

So I'm trying to filter my data by month end and week start. I'm trying to export two databases, one for each filter. 

 

I read about how to filter dates by month end, but I couldn't find anything in regards to week start. I know in excel it would be = date - weekday(date, 2)+1, but what about Alteryx?

 

For week start, what I'm asking is...if I have 1/25/2017 (which is a wednesday), after the week start formula is input, it would show 1/23/2017 (which is the start of that week, a monday).

 

Thanks!

8 REPLIES 8
jgo
Alteryx Alumni (Retired)

Hi @robl93,

 

It's pretty much the same logic you use in Excel, just utilizing Alteryx's syntax.

 

'2017-01-23' = DATETIMEADD('2017-01-25',-TONUMBER(DATETIMEFORMAT([Date],'%w'))+1,'days')

Depending on which week Sundays are to be accounted in, you may need to make adjustments to address. the "%w" specifier is what returns an day number of week, which is a value between 0-6, starting on Sunday.

 

Hope this helps!

robl93
6 - Meteoroid

If I'm understanding correctly, I'd like Monday's to be the start of the week, and Sunday's to be day 7.

 

Also, I'd like this formula to be applied to all the dates in my database (800,000+)

jgo
Alteryx Alumni (Retired)
DATETIMEADD([Date],-
IF
TONUMBER(DATETIMEFORMAT([Date],'%w'))=0 
THEN 7 
ELSE TONUMBER(DATETIMEFORMAT([Date],'%w'))
ENDIF
+1,'days')

Here's a more complex formula that validates the day number. If it's 0 then change to 7 else keep value.

 

The first example I provided hard coded the date in the first argument of DATETIMEADD. simply replace that with the field it needs to reference. On the code I attached here, you'd need to replace all the "[Date]" with your field name (illustrated below).

2017-01-23_16-05-57.jpg

robl93
6 - Meteoroid

I'll give this a go and report back, thanks!

robl93
6 - Meteoroid

Hey jgo,

 

Your formula was perfect!

 

I actually tried to do month end just now and realized I had no idea how to do it either. I'm a total SQL noob. Basically what I'm trying to do for month end is...

 

If I have 2016-11-29 as a date (and thousands of other dates with varying days and months and years), I would like the formula to give me the month end of that particular date. So in this case, it would show 2016-11-30.

 

Thanks in advance once again.

RodL
Alteryx Alumni (Retired)

That one should be a bit easier...there is a DateTimeLastOfMonth() function available in Alteryx.

robl93
6 - Meteoroid

Ah yes, I figured it out. I used:

 

DateTimeTrim([visitdate],"firstofmonth")

dniedrauer
6 - Meteoroid

Simplest formula: 

 

datetimeadd([Date],tonumber("%u")*-1,'days')

Labels