Date Filter x Days from current date

[RobertBob]

I have tried using the [Date] = DateTimeAdd(DateTimeToday(),-1,"DAYS") with no luck at this point. Everything I have found here says this should bloody well work but it continues to vex me. If anyone can help it would be greatly appreciated.   

[RobertBob]

data is here.

What I am trying to do is create an output where the the Summary data is joined to the Historical that is filtered for x days back. I want it to be automatic and set to 7 days. The create the excel sheet TEST.

Hope that makes sense

[RobertBob]

sheesh the alteryx is here

[tcroberts · Accepted solution]

I think the issue is because `DateTimeToday()` gives you date AND time. So you get: '2018-09-13 00:00:00', and since your existing date columns do not have the time, there are no matches.

By changing your filter to:

[Date] = DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"), '%Y-%m-%d')

I was able to get some records output.

Let me know if this is what you were looking for,

Cheers!

[danilang]

@tcroberts has again provided an excellent solution.

The only possible improvement I would make is to reduce the amount of typing you have to do

[Date] = DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"), '%Y-%m-%d')

becomes

[Date] = ToDate(DateTimeAdd(DateTimeToday(),-1,"day"))

ToDate truncates the DateTime value returned by DateTimeAdd to a Date by removing the time information

Dan

[shivarajukuncham]

it solved my purpose really cool.