Filter data for last 12 month

[Shank]

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

Cons	Calendar Month
A	08.2018
A	07.2018
A	06.2018
A	05.2018
A	04.2018
A	03.2018
A	02.2018
A	01.2018
A	12.2017
A	11.2017
A	10.2017
A	09.2017
A	08.2017
A	01.2016
C	03.2018
C	02.2018
C	01.2018
C	12.2017
C	11.2017
C	10.2016

out put

Cons	Calendar Month
A	07.2018
A	06.2018
A	05.2018
A	04.2018
A	03.2018
A	02.2018
A	01.2018
A	12.2017
A	11.2017
A	10.2017
A	09.2017
A	08.2017
C	03.2018
C	02.2018
C	01.2018
C	12.2017
C	11.2017

[Shank]

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

Cons	Calendar Month
A	08.2018
A	07.2018
A	06.2018
A	05.2018
A	04.2018
A	03.2018
A	02.2018
A	01.2018
A	12.2017
A	11.2017
A	10.2017
A	09.2017
A	08.2017
A	01.2016
C	03.2018
C	02.2018
C	01.2018
C	12.2017
C	11.2017
C	10.2016

out put

Cons	Calendar Month
A	07.2018
A	06.2018
A	05.2018
A	04.2018
A	03.2018
A	02.2018
A	01.2018
A	12.2017
A	11.2017
A	10.2017
A	09.2017
A	08.2017
C	03.2018
C	02.2018
C	01.2018
C	12.2017
C	11.2017

[BenMoss · Accepted solution]

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

[Shank]

@BenMoss.

many thanks for your help this helped me a lot. 

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

[Shank]

@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?

[BenMoss]

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

[Shank]

@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

[Shank]

@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?

[BenMoss]

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

[Stuart_C]

Hi Ben,

I used your code for the filter and it works but it's leaving out some dates.  I ran the report as of today but it's only filtering for data from 3/2/19 to 3/1/20.  How do I get it to filter for data from 3/1/19 to 3/31/20?

Previous formula to format date: 

DateTimeAdd("1900-01-01",tonumber([Submit Date]),"days")

Filter for previous 12 months:

datetimediff(datetimetrim(datetimetoday(),'month'),[Submit Date],'month')
<= 12

AND

datetimediff(datetimetrim(datetimetoday(),'month'),[Submit Date],'month')
>= 1

Thank you,

- Stuart

[rpatel524]

If