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

    Filter out all dates except two based on MAX

    Jorge Valdez

      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.

       

      Screen Shot 2018-08-14 at 4.30.09 PM.png

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

          Hi Jorge,

           

          Please try this

           

           

          My datasource

          Screen Shot 2018-08-15 at 5.37.20 AM.png

           

           

           

           

           

          Desired output

          Screen Shot 2018-08-15 at 5.39.02 AM.png

           

           

           

          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 !

          Screen Shot 2018-08-15 at 5.44.41 AM.png

           

           

           

           

          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
            Jorge Valdez

            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
              Ritesh Bisht

              Hi JORGE,

               

               

              Below should help you hopefully , i checked it once again , please try

               

              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