Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Date Range

Highlighted
6 - Meteoroid

Hello, 

I am trying to create recurring Royalty payment reports to pick up through the 1st day of last month, and the last day of last month. 

When I enter the filter below (today 11/21/19 for ex.):

ToDate([inv_date]) >= DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Months"), -1,"Days") AND
ToDate([inv_date]) <= DateTimeAdd(DateTimeAdd(DateTimeLastOfMonth(),-1,"Months"),-1,"Days")

 

It only picks up data min. 10.03.19 through 10.29.19, even though there is data from 10.01.19 and 10.31.19.

 

I wonder of the DateTime is what is causing the issue? Any tips?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @lilianam 

 

Yes, you're right.


Add ToDate() to your DateTimeAdd formula and you'll be fine.

 

Cheers,

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@lilianam 

 

Clarifying.

 

ToDate([inv_date]) >= ToDate(DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Months"), -1,"Days")) AND
ToDate([inv_date]) <= ToDate(DateTimeAdd(DateTimeAdd(DateTimeLastOfMonth(),-1,"Months"),-1,"Days"))

 

Cheers,

Highlighted
6 - Meteoroid

I needed to change my date to date:time, and after the formula you provided above ran, change it back to just Date using the Select Tool and it works.

Thank you!

Labels