3 Replies Latest reply on Aug 15, 2018 12:33 PM by Ritesh Bisht

# Filter out all dates except two based on MAX

I am working with fiscal years and periods. I want to create a filter that always keeps the MAX year and MAX period (in this case 2018 and 10), while also keeping the MAX - 1 year (2017) and the MAX period of that year (12).

I'm not sure if I have to create a new calculated field or if I can just filter this out with a formula through the filter window.

• ###### 1. Re: Filter out all dates except two based on MAX

Hi Jorge,

My datasource

Desired output

Calculation Used

Copy paste --->Drag to Filer and Keep only 'True'

IF YEAR([Date])= YEAR(TODAY())-1

then DATEPART('month' , [Date])=

{ FIXED YEAR([Date]) : MAX(MONTH([Date]))}

ELSEIF YEAR([Date])= YEAR(TODAY())

then DATEPART('month' , [Date]) =

{ FIXED YEAR([Date]) : MAX(MONTH([Date]))}

Here we go !

END

Thanks,

Ritesh

Please mark the answer as CORRECT & HELPFUL if it really helped you so that it can help others as well

• ###### 2. Re: Filter out all dates except two based on MAX

Hi Ritesh,

Thanks for the help. However, I am pulling data from a database and the dates are also fiscal dates, therefore I don't think TODAY will work. So as the data updates I would always like it to be the MAX(year) and the MAX(year)-1. For periods I would always like it to be MAX(period) for the MAX(year) [tableau reads it as month] and MAX(period) for MAX(year)-1.

An issue I also found is that MAX gives back the value as an integer and not a date.

• ###### 3. Re: Filter out all dates except two based on MAX

Hi JORGE,

IF attr(YEAR([Date]))= WINDOW_MAX(max(year([Date])))-1

then attr(DATEPART('month' , [Date]))=

attr({ FIXED YEAR([Date]) : MAX(MONTH([Date]))})

ELSEIF attr(YEAR([Date]))= WINDOW_MAX(max(year([Date])))

then attr(DATEPART('month' , [Date])) =

attr({ FIXED YEAR([Date]) : MAX(MONTH([Date]))}) END

Thanks,

Ritesh

Please mark the answer as CORRECT & HELPFUL if it really helped you so that it can help others as well