8 Replies Latest reply on Jun 28, 2017 2:51 AM by venkat.gunda

    Dynamic previous month and month selection

    Wilson Smith

      Hello,

       

      Is there a way to dynamically run a report for previous month and also have the ability (a dropdown may be) to change the month if need be?


      I have used the below shown relative filter criteria to always run the report for previous month. But using this setup does not give me an option to run the report for January if need be.


      In the attached sample workbook, you wont see any data because i am using superstore set which does not have 2015 data.


      Any help or insight anyone can provide is appreciated.

       

      month_setup.png

       

      Thanks,

      WS

        • 1. Re: Dynamic previous month and month selection
          Kenny Lee

          Hey Wilson,

           

          With the need for a dropdown, a parameter and calculated field will be your best bet.

           

          Try something like this? Where we can use the dateadd function to tell Tableau that we only want to see results for the month before the date selected on the parameter. -1 is telling Tableau to back only by a month.

           

          Screen Shot 2015-07-18 at 11.55.48 am.png

          The display format of the parameter should be customisable, to say "mmmm yyyy" format (July 2013). It's not coming up right on my Mac for now, another issue that I'll be exploring. But hopefully this solution fits your needs for now!

           

          Cheers,

          Kenny

          • 2. Re: Dynamic previous month and month selection
            Wilson Smith

            Hello Kenny,

             

            Thank you for the work around. But this is still making the user select a date using the parameter. I was looking for something that dynamically displays the previous month whenever we open the workbook. Also which will have a dropdown in case the user want to take a look at different month other than previous month.

             

            Thanks,

            WS

            • 3. Re: Dynamic previous month and month selection
              Kenny Lee

              Hey Wilson,

               

              Have you tried exploring with the TODAY() function? So for instance, Calculation 1: DATEADD('month',-1,TODAY())

               

              You'll then need to say IF [Order Date] = [Calculation 1] then [Order Date] END

               

              Last thing is to filter out Calculation 2 to show only non-null dates.

               

              Cheers,

              Kenny

              • 4. Re: Dynamic previous month and month selection
                Dana Withers

                How about something like this:

                If month([Order Date]) = month(DATETRUNC('month', today())-1)

                         and year([Order Date]) = year(DATETRUNC('month', today())-1)

                then 'Previous Month'

                else str(year([Order Date])) + " " + datename('month',[Order Date])

                end

                 

                Basically you rename the previous month to a text of "previous month" - if you save and publish your report with that filtered, it will always filter on the previous month (recalculated on the spot). Plus your users will have a list of months to pick from. I've formatted that list to start with year to help with ordering.

                 

                (btw this is hard to test with the superstore as there is no previous month data, but I tested it by replacing today with a date parameter that was set to September 2013)

                 

                Hope that helps,

                 

                Dana

                1 of 1 people found this helpful
                • 5. Re: Dynamic previous month and month selection
                  Rody Zakovich

                  Hello Wilson,

                   

                  One idea is to use a parameter and a Quick Filter.

                   

                  Your parameter would be a string list with two options.

                   

                  Select Date =>

                  Previous Month

                  Select Month

                   

                  Then create a Boolean filter that always equates to true.

                   

                  IIF ([Select Date] = 'Previous Month'

                  , DATETRUNC('month', [Order Date]) = DATETRUNC('month', DATEADD('month', -1, { MAX([Order Date]) }))

                  , [Order Date] = [Order Date]

                  )

                   

                  Drag that onto your filter shelf and Select True. Then Drag your DATE onto the Filter show and show Quick Filter. Change it to MonthYear and single select dropdown and change it to use only Relevant Values.

                   

                  Then leave the Parameter defaulted to last month. If the user wants to change the month, they simple change the Parameter to Select Date and the Quick Filter kicks in.

                   

                   

                  Example uses Superstore where the MAX date is December 2013.

                  7-21-2015 8-33-29 AM.png

                   

                  7-21-2015 8-34-16 AM.png

                   

                  Hope this helps,

                   

                  Regards,

                  Rody

                  1 of 1 people found this helpful
                  • 6. Re: Dynamic previous month and month selection
                    Wilson Smith

                    Hello Dana,

                     

                    This is very close to what i am looking for. I wanted the month name spelled out for the previous month rather than a text that displays 'previous month'. But i guess this is the closest we can get.

                     

                    Thanks to Rody and Kenny as well for providing different workarounds.

                     

                    Cheers,

                    WS

                    • 7. Re: Dynamic previous month and month selection
                      Dana Withers

                      Hello,

                       

                      You are right - this is the closest you can get. The moment you spell out the previous month's name, the filter will get stuck on that name when you save/publish and it will not be correct the next month anymore - I tried that because I wanted the same thing initially. That is why Tableau has the dynamic option, but that then does not allow for easy selection of other months in a list. My users were happy with this solution though.

                      Hope yours will be also.

                       

                      Good Luck!

                       

                      Dana

                      • 8. Re: Dynamic previous month and month selection
                        venkat.gunda

                        hi

                        pls send the workbook what you developed

                         

                         

                         

                         

                         

                         

                        Thanks

                        venkat