8 Replies Latest reply on Oct 31, 2013 8:48 AM by Matt Lutton

    Last 12 months calculated field

    philip ken

      My company data base has many years data but I want to filter (in tableau worksheet) such way that I can show last 12 months data including current date. How to write calculated field on this requirement? This is a dynamic field. Just say if I ran the report on Oct 10, 2013, then data supposed to be display November 01, 2012 to Oct 10, 2013 and so on…

        • 1. Re: Last 12 months calculated field
          Matt Lutton

          You can use a date field on the Filter shelf, as a continuous (green pill), and choose "Relative Date", and you can set this to the Last 12 months.  Note that I have two versions of Order Date in play--Month/Year discrete (blue pill) on Columns shelf, and Continuous green pill on the filter shelf.  These pill types are critical in getting Tableau to do what we want, so its important to learn how they behave differently.  Putting a blue date pill on the filter shelf allows you to choose discrete dates to filter on, while a green pill will allow you to set a continuous range of dates to filter on.

           

          There are other ways to do this, but this is by far the simplest.  If it doesn't fit your needs, please post a sample packaged workbook (.twbx file) so we can take a look and help you get what you need.

           

          Screenshot of filter:

          Relative Date Filter.png

           

          Simple example attached using sample data provided.

          • 2. Re: Last 12 months calculated field
            philip ken

            Thank you Lutton your prompt reply. But this is not what I am lokking for. If I defined relative date for last 12 months, all data will not displayed in quick filter. I want all date supposed to be displayed in quick filter for last 12 months.

            I need your help  how to write a SQL statement is calculated field.

            • 3. Re: Last 12 months calculated field
              Matt Lutton

              I do not understand what you mean by "not displayed in quick filter".  I don't believe a SQL statement is needed, but if you can explain what you expect the result to be in more detail, we can help you get there.

              • 4. Re: Last 12 months calculated field
                philip ken

                I meant once you defined the relative date for last 12 months and added in Quick filter, it will show Last 12 months (text) in values. I want all the dates instead.

                • 5. Re: Last 12 months calculated field
                  Matt Lutton

                  Please post a packaged workbook file (.twbx) in order to get the help you need.  I have no idea what dates you expect to show up--I'd need to see your workbook to understand what you are trying to accomplish.  Again, if you can explain in more detail, or post a mock up of the expected result, I will do my best to help.

                  • 6. Re: Last 12 months calculated field
                    Matt Lutton

                    Here is an example using a calculated field to filter to the last 12 months--the part after the AND was used to remove future dates from this particular data set (so its not necessary if you don't have future dates):

                     

                    IF DATEDIFF('month', [Order Date], today()) <=12

                    AND [Order Date] < today()  // Only needed if you have future dates

                    then [Order Date]

                    end


                    The duplicate of this calculation was used to exclude the NULL value from the quick filter selection.

                     

                    See attached--again, a discrete pill of the date field will list out all the available dates in your quick filter, as explained before.

                     

                    Again, there are many ways to accomplish filtering in Tableau--the path taken will depend on what you expect as a result.  This is why packaged workbooks are most helpful in answering questions--I am really just making guesses about what you want.

                    • 7. Re: Last 12 months calculated field
                      philip ken

                      Thank you Lutten. This is what I am looking for.

                      • 8. Re: Last 12 months calculated field
                        Matt Lutton

                        No problem at all!  As a tip, for future posts on the Forum, I would recommend mocking up a scenario in the Sample Data provided by Tableau.  It is much easier to answer questions with a visual to refer to, plus we can see the setup you are using for the particular view in question.  Many questions without workbooks attached go unanswered, so posting a packaged workbook is certainly the best way to ensure you get the help you need.

                         

                        Cheers!