4 Replies Latest reply on Sep 5, 2018 1:41 PM by Brandon Kidwell

    Last N Months and Next N Months

    Ashley Inman

      I have a date that has future dates. I would like to allow my user the ability to decide how many months back and how many months forward they would like to see.

       

      I'm struggling to get my Parameters to work correctly to start at the current month and go back and forward months.

        • 1. Re: Last N Months and Next N Months
          Brandon Kidwell

          Hello Ashley,

           

          This should help you build your parameters quickly.  Creating a Filter for Start and End Dates Using Parameters | Tableau Software 

           

          You should be able to anchor one of the parameters to Month(Today()) and use the 2nd Parameter to decide the date whether it's before or after the Anchor date. 

           

          I hope this helps.

           

          Best,

          Brandon

          1 of 1 people found this helpful
          • 2. Re: Last N Months and Next N Months
            Brandon Kidwell

            Hello Ashley,

             

            I found one modification that might help.  There are some drawbacks but you can put a conditional parameter on Forecast vs. Actual and then create an IF statement that flips the Start and End date whether it is forward looking or past.

             

            Dates_1.JPG

             

            Create a Parameter to Select Forecast or Actual (or preferred naming convention)

             

             

            Dates_2.JPG

             

            Then Create a formula to switch the Date range from backward looking to forward looking. 

             

            IF [Forecast or Actual] = "Forecast" THEN [Date] >= [Parameters].[Start Date] AND [Date] <= [End Date]

            ELSEIF [Forecast or Actual] = "Actual" THEN [Date] <= [Parameters].[Start Date] AND [Date] >= [End Date]

            END

             

            Dates_3.JPG

             

            Drag this formula to Filters = TRUE

            then show your parameter controls for Start Date, End Date and your Forecast vs. Actual.

             

            This has some drawbacks as it could become confusing for the untrained user but I might lead you to a better alternative.

             

            I hope this helps.


            Thanks,
            Brandon

            • 3. Re: Last N Months and Next N Months
              Ashley Inman

              Hi Brandon,

               

              These are really helpful thank you! I'm wondering if there's a way for me to have the parameters as integers rather than having the user select dates?

               

              I haven't been able to figure this part out with looking at today's date as the Past N months and Future N Months. Do you know is this possible?

               

              For example with the screen shot below, I want the date ranges to be the last 6 months from today and the next 6 months from today.

              • 4. Re: Last N Months and Next N Months
                Brandon Kidwell

                Hello Ashley,

                 

                I think that's a great idea.  Replace your Start and End Dates with Last and Next.  The challenge is getting the Parameters to anchor to Today() or Month of Today() and working for either, or, or both.  Let me know what you find, I'll share if I can find an alternative using integers.

                 

                Thanks,

                Brandon