9 Replies Latest reply on Feb 23, 2018 1:04 AM by kirti Trivedi

    latest 12 month data based on filter selection

    Sid Singh

      Hi All,

       

      I am using superstore data to post a question here. I using a crosstab with ship date and sales, what i need is that based on month-year selection in the filter i should be able to see the last 12 months of data only.

       

      Currently i can achieve this with parameter and a calculate field which then placed on filter shelf and by selecting "true". The problem which i face in that is that the parameter needs to be updated manually everytime the data refreshes.

      Parameter gets the value from ship date and it is not dynamic which i think is the limitation of Tableau.

       

      Can anyone suggest that how i can achieve the same with filter.

        • 1. Re: latest 12 month data based on filter selection
          Sharad Adhikari

          Hi,

           

          I might have misunderstood you but I would just drag Ship Date to Filters. Choose Relative date --> Choose Months, Last 12 months. If you anchor relative to Today, it will be dynamic all the time..

           

          BR

          Sharad

          • 2. Re: latest 12 month data based on filter selection
            Steve Martin

            Hi,

             

            Sharad's suggestion is correct although it would be useful in understanding how you reached this more complex setup than simply creating the filter as suggested. Are you looking for a method that allows for greater flexibility than a simple filter for example, are you wanting to use your parameter to show other date-periods but to be defaulted to the last 12 months?

             

            Steve

            • 3. Re: latest 12 month data based on filter selection
              Sankarmagesh Rajan

              Hi Tableau,

               

              We can use allowable values as All in parameter and create reports, it will show past and present dates in parameter. Better if you need to show selected date to go last 12 months then this will help.

               

              If you want to show only last 12 months then go with calculation using last()<12.

               

              Thanks

              sankar

              • 4. Re: latest 12 month data based on filter selection
                Sid Singh

                I have achieved this with relative filters, what I want is there is dropdown filter and then when a user selects a month-year, I should see last 12 months data in the crosstab.

                • 5. Re: latest 12 month data based on filter selection
                  Sid Singh

                  so I know that this can be achieved through a parameter, but with a parameter the problem is that if I take values in a parameter from a field in this case lets say ship date and then use that parameter in calculated field to show last 12 months data it works perfectly.

                   

                  It fails when lets say data refreshes and the user has to go everytime in the workbook to manually update the parameter.

                   

                  So I am looking for a dropdown filter not a relative filter, which should work on a single selection and still shows me the last 12 month values based on month year selected.

                   

                  hope this helps

                  • 6. Re: latest 12 month data based on filter selection
                    Steve Martin

                    I get you, basically, you are looking for an option which does not require manually updating such as updating the parameter.

                     

                    I'm guessing you want to still allow your user to select the start month eg April and your data should filter to the last 12 months of April 15 to March 16 or if they select February then you want to bring through from Feb 15 to Jan 16 etc, is this correct?

                     

                    This can be easily achieved using a correctly scoped last function - it may even be that the default table down will be sufficient; though you may need to think about how your logic works, which can be using the Month(Today()) function in order to ascertain the starting position for example:

                     

                    If you want to see the last 12 month from today in a table containing 24 months of data then you filter would be something like (when scoped to month): Last() >=0 And Last() <= 12

                     

                    The above will only work with hard-coded numbers which is where the logic comes in, in order to determine the correct positioning then you would need to create positioning logic such as:

                    First off, set your parameter to be integer running 0-12, 0 = current And then Jan to Dec = 1-2

                     

                    And then create the positioning: [Start] Month(Today()) - <Date Parameter>

                     

                    [End] 12 + [Start]

                     

                    So what this does is use your defined month to alter the value of the Last range so you can change your calc to be:

                     

                    If <Date Parameter> = 0 Then Last() >=0 And Last() <=12

                    Else Last() >= [Start] And Last() <= [End]

                    End

                     

                    This will still be boolean so will carry a true|false allowing you to simply set true.

                     

                    Steve

                    • 7. Re: latest 12 month data based on filter selection
                      Poonam Shah

                      Hey, Are you able to solve this issue.

                      I have the similar problem and looking for suggestions.

                      • 8. Re: latest 12 month data based on filter selection
                        madhuri.tanniru

                        Hi Poonma,

                         

                        I have put together a workbook that displays 12 months of data by inputting a date as a parameter.

                         

                        Please see if this could be a starting pint to what you want to build.

                         

                         

                         

                        Thanks,

                        -Madhuri

                        • 9. Re: latest 12 month data based on filter selection
                          kirti Trivedi

                          Did you get resolution to the problem, I have a similar one and looking for solution.