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

Filter data for last 12 month

Shank
8 - 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

 

9 REPLIES 9
BenMoss
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

@BenMoss.

 

 

many thanks for your help this helped me a lot. 

 

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

Shank
8 - 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?

 

 

BenMoss
ACE Emeritus
ACE Emeritus

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

Shank
8 - 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?

BenMoss
ACE Emeritus
ACE Emeritus
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
7 - Meteor

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
5 - Atom

If 

Labels