forums

Use these forums to ask questions and discuss Tableau.

Filter for the latest 2 years data

Posted April 22nd, 2008 by Andy So
in Dates & Times

Hi,

I am looking for a way which I can filter out the updated last 2 year data everyday I am using that filter in Tableau.
Eg: I want to get data from 01 April 2006 to 31 March 2008 now but when I run the query again next month, I want to get data from 01 May 2006 to 30 April 2008. I tried writing something like year([date]) - year(now()) < 2, I get some aggregation error. Can anyone teach me how to write that in the function? Thanks a lot.

Andy

Add new comment

Comments

On April 22nd, 2008 James Baker said:

([date] - now()) will always be 0 or negative. First start by swapping those. Then, "now" gives you a date+time, while "today" gives you just a date. The "year" function doesn't care about that, but it could affect you elsewhere. "TODAY( ) - [Date] < (365*2)" will give you roughly the last two years, unless you're trying to do something with more precise month beginnings/endings.

reply
On April 22nd, 2008 Aaron said:

From your example, it sounds as though you want something a bit more precise than a simple two-year range. You actually want to restrict your date range to the 24-month period ending with the previous month. If that is the case, create the following calculated field:

Name: MonthsAgo
Formula: 12*YEAR(TODAY())+MONTH(TODAY())-12*YEAR([Date])-MONTH([Date])

Then create a filter on [MonthsAgo] restricting its values to the range 1-25.

The MonthsAgo field calculates the number of months prior to the current month taking into account both the year and the month of the date (essentially it converts each date to a 'month number') then calculates the difference between the month number today and for each data point.

Hope this helps!

reply
On April 22nd, 2008 Andy So said:

Thanks Aaron. It works very well. Thank you so much!

reply
On April 22nd, 2008 Andy So said:

Thanks James for your recommendation.

reply