5 Replies Latest reply on Feb 5, 2018 8:16 AM by Stephen Morgan

    Filter by Parameter - Date Range

    Stephen Morgan
    Stephen Morgan Feb 5, 2018 7:10 AM

      Hi - I have a parameter [Period] based on a date object and returns Month and Year. I need to restrict the dataset using this parameter so that the range will go from the first month of the previous year to the current month parameter selection. For example -

       

      Period Selection: February 2018 should return dates from Jan 2017 to Feb 2018

      July 2018 should return Jan 2017 to July 2018 etc...

       

      I have tried to create MIN and MAX dates around the parameter respectively as below -

      [START DATE MIN]:    YEAR([Period])-1 

      [START DATE MAX]:   [Period]

       

      then use them in the main date object as below -

       

      START DATE >= [START DATE MIN] and START DATE <= [START DATE MAX]

       

      however errors with 'Can't compare Datetime and Integer values'

       

      Can anyone help with this? Thanks.

       

      (Tableau 10.1)

      • 47 Views
      • Tags:
        • 1. Re: Filter by Parameter - Date Range
          Jim Dehner
          Jim Dehner Ambassador Feb 5, 2018 7:26 AM (in response to Stephen Morgan)

          Hi Stephen

          several ways t do this - below is a formula that if a t/f filter

           

          DATETRUNC('year',([Parameter 3]) )= DATETRUNC('year',[Order Date])

          and DATETRUNC('month',[Parameter 3])>= DATETRUNC('month',[Order Date])

          OR

          DATETRUNC('year', dateadd('year',-1,([Parameter 3]) ))= DATETRUNC('year',[Order Date])

          and MONTH([Parameter 3])>= MONTH([Order Date])

           

           

          it will return this

           

           

          I included the months just for the example you can exclude them and the filter will  return the total

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Filter by Parameter - Date Range
            Stephen Morgan
            Stephen Morgan Feb 5, 2018 7:57 AM (in response to Jim Dehner)

            Hi Jim

            Many thanks for this. Almost there but it only returns the corresponding period from the prior year e.g. (I'm working on a quarterly basis) for Q1 2018 it only  returns Q1 2017 where I need Q1, Q2, Q3 and Q4 for 2017 plus Q1 for 2018.

            • 3. Re: Filter by Parameter - Date Range
              Stephen Morgan
              Stephen Morgan Feb 5, 2018 8:16 AM (in response to Stephen Morgan)

              I've removed the last month clause and looks ok. Thanks again.

              • 4. Re: Filter by Parameter - Date Range
                Jim Dehner
                Jim Dehner Ambassador Feb 5, 2018 8:06 AM (in response to Stephen Morgan)

                if this meets the need pleas mark my reply correct to close the thread

                 

                if you are looking at Quarters specifically the datetrunc formula will recognize datetrunc('quarter',your parameter')  which will be easier to use than month

                the way datetrunct works is it will look at the first day of the quarter i.e. Jan 1, April,1 Jul1, Oct1 and those can be used in the comparison

                 

                DATETRUNC('year',([Parameter 3]) )= DATETRUNC('year',[Order Date])

                 

                 

                and DATETRUNC('quarter',[Parameter 3])>= DATETRUNC('quarter',[Order Date])

                 

                 

                will look at the current year through the entire current quarter

                 

                Jim

                 

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.