Simplification for data filter

[ulrich_schumann]

Hi all,

we run a weekly report. This report collects all entries from last week only. Currently we use a filter to get the data for this week. Every week we need to change the filter manually to adjust the last week (based on the calendar days). The report is created on different days within the week, but we always need to report the entire last week. Is there any change to make this more SIMPLE?

[pcatterson]

You would you the DateTimeToday() and DateTimeAdd() tools to accomplish this.  Something like my example.

[jdunkerley79 · Accepted solution]

You can use the DateTimeFormat function and the  week number formats to help:

DateTimeFormat([Date],"%U")

This returns the WeekNumber of the Data as a string. You can compare it to the equivalent from a week ago:

DateTimeFormat(DateTimeAdd(DateTimeToday(),-7,"days"),"%U")

This will not work around year ends but would work otherwise. 

If you want one that would work around year end then you will need to do a little more hoops:

[Date] >= DateTimeAdd(DateTimeToday(), ToNumber(DateTimeFormat(DateTimeToday(),"%w"))-7, "days")
and
[Date] < DateTimeAdd(DateTimeToday(), ToNumber(DateTimeFormat(DateTimeToday(),"%w")), "days")

This works out the explicit start and end dates so works across year boundaries

Sample attached.

[ulrich_schumann]

With your attached example, I'll see data from this week as well but I am only interested in last week calendar days.

[ulrich_schumann]

Hi jdunkerley79,

our calendar week starts in Germany on a Monday. What would I need to change in order to reflect this?

[jdunkerley79 · Accepted solution]

If using the week number method the use %W instead of %U (take a look at http://downloads.alteryx.com/Alteryx8.6.2/WebHelp/Reference/DateTimeFunctions.htm).

If using the other method:

[Date] >= DateTimeAdd(DateTimeToday(), ToNumber(DateTimeFormat(DateTimeToday(),"%w"))-6 "days")
and
[Date] < DateTimeAdd(DateTimeToday(), ToNumber(DateTimeFormat(DateTimeToday(),"%w"))+1, "days")

should work 

[ulrich_schumann]

You guys are really amazing - thank you!

[pcatterson · Accepted solution]

Sorry I misunderstood, I thought you wanted the adjacent 7 days, not the last Monday to Sunday timeframe.  This is very similar to JD's solution, but heavier on the IF-THEN-ELSE: