Future week number projection using today's date

Question

Good Day,

I'm trying to automate week number projection for a weekly report. First, how can I get the week number as a filter, and second for the same filter, how can I get the filter to call the data with 2 weeks or three weeks ahead(Week number)?

Thanks

ddiesel · Answer

Hi @minatawadros!

I second @Prometheus suggestion for the formula and the link he shared is a go-to for me.

To answer your question about the filter, here's one way to do it:

DateTimeFormat([Your_Date],'%W') = DateTimeFormat(DateTimeAdd(DateTimeNow(),14,'days'),'%W')

The first part of the formula converts your date into a week number. The second part finds the week number 14 days from now. If the week numbers are equal, it passes through the true anchor.

A few more tips:

%W returns the week number, as 00 – 53, with the beginning of weeks as Monday. If you need the week to start on Sunday, try %U instead.

If you need to go three weeks ahead instead of two weeks ahead, add 21 instead of 14 days.

If you need a range of 2-3 weeks, add an 'and' clause to your custom filter to build in a start and end date for your filter.

Hope this helps!

Prometheus · Answer

@minatawadros You can use the expression DateTimeFormat([Date], '%W') to get the week number. This help page has been super useful for me over the years: https://help.alteryx.com/20231/designer/datetime-functions.