community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Filter data for last 12 month

Asteroid

Hi All,

 

I have a set of data and where i need to filter it for last 12months can someone help on this. and also, it should act as a dynamic filter. 

 

For Example: current month is 08.2018 then it should filter data from 08.2017 to 07.2018

 

below is my scenario.

 

Input

 

ConsCalendar Month
A08.2018
A07.2018
A06.2018
A05.2018
A04.2018
A03.2018
A02.2018
A01.2018
A12.2017
A11.2017
A10.2017
A09.2017
A08.2017
A01.2016
C03.2018
C02.2018
C01.2018
C12.2017
C11.2017
C10.2016

out put

 

ConsCalendar Month
A07.2018
A06.2018
A05.2018
A04.2018
A03.2018
A02.2018
A01.2018
A12.2017
A11.2017
A10.2017
A09.2017
A08.2017
C03.2018
C02.2018
C01.2018
C12.2017
C11.2017

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi Shank.

 

We can achieve this using some of the functions that we utilised in the solution for your post yesterday (datetimetoday() and datetimetrim()).

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Generate-rows-dynamically-for-month/m-...

 

We also need the filter tool and the datetime parse tool.

 

Our filter statement would be...

 

datetimediff(datetimetrim(datetimetoday(),'month'),[Calender Month],'month')
<= 11

Please see the attached solution.

 

Ben

 

 

Asteroid

@BenMoss.

 

 

many thanks for your help this helped me a lot. 

 

i was using differnet formula and was not able to get that.

Asteroid

@BenMossit should filter from 08.2017 to 07.2018 but in current case it is filtering from 08.2018 to 09.2017 how can we fix this?

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Have a go at making some amendments to the filter function used. Once you have tried let us know if you still need help. Let us know what you have tried too.

 

Ben

Asteroid

@BenMossi tried amending the formula by applying month -1 but still i am able to see the 08.2018 data.

 

datetimediff(datetimetrim(DateTimeAdd(DateTimeToday(),-1,'month'),'month'),[Calender Month],'month') <= 11

Asteroid

@BenMossi tried amending the formula by applying month -1 but still i am able to see the 08.2018 data.

 datetimediff(datetimetrim(DateTimeAdd(DateTimeToday(),-1,'month'),'month'),[Calender Month],'month') <= 11

 

I should include >=0 and <=11 so that we can fix this :)

 

i am able to fix this using the another calculation field and then filtering it >=0 and <=11

 

is there any other way so that we can directly do it in filter?

Alteryx Certified Partner
Alteryx Certified Partner
datetimediff(datetimetrim(datetimetoday(),'month'),[Calender Month],'month')
<= 12

AND

datetimediff(datetimetrim(datetimetoday(),'month'),[Calender Month],'month')
>= 1

Should do the trick. Place this in the filter tool instead of the existing statement.

 

Ben

Labels