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

[Serena3]

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..

[CRowlatt · Accepted solution]

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

[neilgallen · Accepted solution]

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

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

[danilang]

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

Becomes this

Dan