1 Reply Latest reply on Dec 18, 2015 5:34 PM by Esther Aller

    How do I apply a data source filter for an extract that includes the max month of a string year-month field, the previous 3 months, and last year's data for 2 months?

    Robb Hughes

      I have a data set with a field called year_month that is a string formatted yyyy-mm. The data set has 16 months of data beginning 9/2014 and ending 12/2015.

       

      I want to implement a data source filter where the extract only includes the max month in the data (in this case 12/2015), and the previous 3 complete months of data (9/2015, 10/2015, and 11/2015). I'm also interested for YoY calculation purposes to include last year's December and November months.

       

      I've made a few attempts to this and frequently run into the error: "Cannot mix aggregate and non-aggregate arguments".

       

      Any suggestions?

       

      Thanks!

      Robb

        • 1. Re: How do I apply a data source filter for an extract that includes the max month of a string year-month field, the previous 3 months, and last year's data for 2 months?
          Esther Aller

          Hi Robb,

           

          Calculated fields can be used in data source or extract filters, however we cannot use table calculations. Table calculations depend on how the view is created and therefore are not the best choice when we want to find the most recent month in the data set.

           

          A calculation that filters the data source to show only the most recent month and 3 previous month may look like:

           

          [Date] >= DATETRUNC('month', DATEADD( 'month', -3, { FIXED : MAX( [Date] ) } ) )

           

          There's a lot going on in the calculation above. To begin, we are fixing the maximum (or most recent) date at the data set level. When no dimensions are included in a level of detail (LOD) expression, then Tableau Desktop fixes the expression to the data set level.

           

          Because we want to include the previous 3 months, we use DATEADD() to move the cutoff date back 3 months.

           

          If the most recent date in the data set is not the first of the month, then the date cutoff will be in the middle of the month 3 months back. To fix this we use DATETRUNC() to set the cutoff date to the first day of the month.

           

          Now that we have the correct cutoff date, we can just return dates that are after or equal to the cutoff date.

           

          I hope that answers your question!