Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Filter Data to only use 'month to date' rows (including previous years)

Highlighted
Meteoroid

Hi all,

 

I have a table of data with one of the fields 'Date' containing dates for 2018 or 2019 e.g. "20/9/2018", "13/2/2019" etc.

Now there will be rows for any month of 2018 but for any 2019 rows the months only goes up to July (i.e. the current month).

 

I want to filter the data so that the output table contains everything except the July to December 2018 rows (i.e. the current month we are in today to December for the previous year) as well as exclude the current month we are in for the current year (i.e. July 2019)

This extraction is to be automated too. For example in future, when it's December 2019 the data is filtered to exclude December of 2018 and December of 2019.


[The aim down the track is to compare January 2019 with January 2018 and compare February 2019 with February 2018 etc... ]

 

I hope that makes sense!

 

Any help will be greatly appreciated! I'm new to alteryx so still getting used to it..

 

Asteroid

Hi Serena3,

 

You should be able to use the formula tool and a DateTimeMonth function to create a month only column to return the month only value, this will give you a set of numbers 1-12 for each lines of data. Both Jan 18 and Jan 19 will be 1, Feb 18 and 19 will be 2, etc etc.

 

Then you need to just have a filter with a calculation like:

 

[Month] < DateTimeMonth(DateTimeToday())

 

All trues will have a month number less than the current month number. So you can compare a current year to date (until the end of previous month) with the same period last year.

 

Hope that makes sense. (and hope i've understood the question)

 

Chris

 

 

Highlighted
Quasar
One filter tool is all you’d need with the following expression:

Datetimemonth([date]) < datetimemonth(datetimetoday())

Highlighted
Castor
Castor

Hi @Serena3 

 

Since you want to exclude the current month as well as the current month from the previous year to the end of the previous year, you'll need a multi-part filter

 

[Date]<toDate(DateTimeFirstOfMonth()) and 
!([Date]>=todate(datetimeadd(DateTimeFirstOfMonth(),-1,"years")) 
    and 
  DateTimeYear([Date])<datetimeyear(DateTimeToday())
  )

 

The first criteria strips out everything after the start of the current month.   The second part returns everything that is not between the start of this month last year and the end of last year. 

 

So this

 

Input.png

 

Becomes this

 

output.png

 

Dan

Labels