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

How to Filter out Current Month

whitkrieng
8 - Asteroid

Hi all,

I have a dataset that has this for example:

 

Activity_timeframeSample
5/1/2017150
6/1/2017100
7/1/2017120
8/1/2017140
9/1/201725

 

i want to remove 9/1/2017 because it isn't a full month yet.  

 

I tried in the "Filter" tool to put in: 

activity_timeframe < DateTimeFirstOfMonth()

 

It appears to not have worked.  Any guidance would be appreciated, thanks for your help.  

8 REPLIES 8
nick_ceneviva
11 - Bolide

What you have should work!  I think what is probably happening is that your Activity_timeframe column is not formatted as a date.  You will probably need to use the DateTime Parse tool to convert the field.

danrh
13 - Pulsar

It's likely a formatting issue.  I'd check if your dates are formatted as dates --- if not, use the DateTime tool to convert them.  Once you've verified your dates are good to go, then try adding DateTimeFirstOfMonth() in a formula prior to the filter and setting the Data type to Date.  I've had issues comparing Dates to DateTimes, so this might be the cause.

 

image.png 

whitkrieng
8 - Asteroid

Thanks for your response, Nick.

It was originally a string and I did convert it to a Date which I was able to confirm.  

 

Then I just used that formula in the filter, there were no outputs in the False output which there should have been if it is able to filter out the current month.  

 

 

 

 

nick_ceneviva
11 - Bolide

Very strange.  I just tried it out and got the same thing, so something weird must be going on with the date comparison.  This filter will work though:

 

DateTimeDiff(DateTimeFirstOfMonth(),[Activity_timeframe], "days")>1

whitkrieng
8 - Asteroid

Oh this actually worked, some reason I have to create separately a FirstofMonth type dimension with the Formula tool.  Then used that within the filter, which is weird.  It's an extra step in the workflow but did work.  

 

I will try anyways your way too Nick just for learning purposes.  Thanks again for your help.  

novice1
8 - Asteroid

hi, so i have data as below. this is total data for a year from 01-01-2018. i need to filter out only current month.

STORE_NBRODYTD VolumeYTD SalesCurent month
41263853215385231.301/07/2019 00:00
41273827740244259.4201/07/2019 00:00
41313855921545563.5601/07/2019 00:00
41353839630311489.0101/07/2019 00:00
41363830872277936.7201/07/2019 00:00
41373847486391730.5301/07/2019 00:00

 

how can  i achieve this?

 

any advice would be much appreciated.

 

novice1
8 - Asteroid

current month = 1st day of the current month

DJT75
5 - Atom

It didn't work for me, when I use the formula in custom filter ToDate([date]) < DateTimeFirstOfMonth() 

it leaves out all with day 1. for example 10/1/2021 data is left in true. PLease advise

 

Labels