Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
13 - Pulsar
13 - Pulsar

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