Dyamic date filter

[DavidOliverChapman]

Hi community

I have a dataset which is updated each month. One of the outputs is a summary of transactions for the most recent four quarters (ie. Q3,Q4 2018 and Q1,Q2 2019).

Currently, I am using a fixed date filter to filter the data. Ideally, I would like to introduce a filter which will provide the most recent four quarters based on the current date, so that once 30/09/2019 is passed, the quarter range will shift to Q42018 and Q1,2.3 2019).

I wondered about a custom filter which would be (excuse the poor syntax):

[Status_date] > (today)-365. However such a filter (even if written correctly) would result in part of Q3-2019 and part of Q3-2018. 

Once again your help is always appreciated and invaluable.

DOC

Best

David

[JosephSerpis · Accepted solution]

Hi @DavidOliverChapman I mocked up a workflow that I think I could help. Essentially I work out the quarter based on the date then rank them to get the latest 4 quarters and filter anything that isn't in the last 4 quarters.Let me know what you think?

[Kenda · Accepted solution]

Hi @DavidOliverChapman 

There are a couple ways to accomplish this. If you want to do it with just two tools, here is a solution:

Add a Formula tool to create a new field with the following expression:

datetimeparse(tostring(iif(tonumber(datetimemonth(DateTimeToday())) < 4, 1,iif(tonumber(datetimemonth(DateTimeToday())) < 7, 4, iif(tonumber(datetimemonth(DateTimeToday())) < 10, 7, 10)))) + "-1-" + tostring(DateTimeYear(DateTimeToday())),"%m-%d-%Y")

Basically, this will create a field that has the start date for the current quarter you're in. It looks long and complicated, but really it is just checking the month number for the current date, deciding what the month number is for the first month of this quarter, then building the date by adding the day number and year. I called my new field Quarter Start.

From there, all you need to do is filter with this expression:

 [Status Date] >= DateTimeAdd([Quarter Start],-1,"year") && [Status Date]<[Quarter Start]

[DavidOliverChapman]

@Kenda @JosephSerpis 

Thanks both. I have tried both of these solutions in my workflow, and they both make sense.

Again - thanks for your help and intelligence in solving these problems.