Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Unable to filter future months

JinOnn
8 - Asteroid

Im trying to identify items with end of month dates. In this scenario, i want to filter only items on 30th of June and 31st of July.

 

There is 1 line item thats falls under the 31st of July however i tried using the formula shown below but it didn't work. 

 

I am able to get items from before June but not after.

 

Any advise?tempsnip2.pngtempsnip.png

 

 

12 REPLIES 12
jamielaird
14 - Magnetar

Hi @JinOnn ,

 

Try this, which tests if a date is the last of the month by checking if date + 1 day has a different month:

 

DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])

 

jamielaird_0-1624360094681.png

 

KarolinaRoza
11 - Bolide

Hi @JinOnn ,

 

in the second part of your formula you are getting : 2021-07-30 ( 2021-06-30 + 1 day).

 

Please try below:

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month")) OR

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

 

regards,

Karolina

KarolinaRoza
11 - Bolide

Hi again,

 

Actually below formula is enough for each month last day:

Karolina

 

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

KarolinaRoza_0-1624360770794.png

 

JinOnn
8 - Asteroid

Still not able to get what I want.

 

Im using the filter function to get the last day of the month for June and July. 

However, the formula (as attached) was not able filter data in July. 

JinOnn_0-1624361972839.png

 

However, when i use -1 or -2 as my interval, it works! I am just not able to use  1 to capture July's date

JinOnn
8 - Asteroid

Hey Karolina!

 

I think you're close , however in this scenario I only want the dates for the month of June and July.

 

Is there another syntax I can try?

jamielaird
14 - Magnetar

Hi @JinOnn ,

 

Have you tried the solutions posted by @KarolinaRoza or I?

 

DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])

 

[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))

 

If these don't give you the answer you want, can you clarify what you are trying to achieve?

JinOnn
8 - Asteroid

Karolina is close! However, I want to only get the end of month date for June and July. Karolina's formula allows me to get all the end of month date from previous months.

 

tempsnip3.png

What I am looking for is as shown above. The date time add formula works for -1 or -2 as my interval but it doesn't work for future months.

 

 

 

 

DawnDuong
12 - Quasar
12 - Quasar

Hi @JinOnn 

You can combine what @KarolinaRoza  suggested with the DateTimeMonth function to limit the months to June and July only.

E.g.

 

[DateTime_Out]= DateTimeTrim([DateTime_Out],'LastOfMonth')

AND

(DateTimeMonth([DateTime_Out]) = 6 OR DateTimeMonth([DateTime_Out]) = 7)

KarolinaRoza
11 - Bolide

Hi @JinOnn ,

 

I am not sure if your formula works correctly for previous months, if I apply:

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-1,"month"))   -> receiving: 2021-05-30 - not correct, as last May day is: 2021-05-31

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"))   -> receiving: 2021-04-30 - OK

 

Please try:

 

[DateTime_Out]=todate(DateTimeTrim(DATETIMEADD(DateTimeToday(), 1, "month"),'lastofmonth')) OR

[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month"))

 

it should work.

 

Karolina

Labels