Week Start formula?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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+)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'll give this a go and report back, thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That one should be a bit easier...there is a DateTimeLastOfMonth() function available in Alteryx.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ah yes, I figured it out. I used:
DateTimeTrim([visitdate],"firstofmonth")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Simplest formula:
datetimeadd([Date],tonumber("%u")*-1,'days')
