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 |
Solved! Go to 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()).
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
many thanks for your help this helped me a lot.
i was using differnet formula and was not able to get that.
@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?
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
@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
@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?
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
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
If