6 Replies Latest reply on Aug 7, 2018 6:14 PM by Brian Seefeldt

    How to show 'n' months back from month selection filter?

    Brian Seefeldt

      Tableau Desktop Version: 10.5

      Tableau Server: 10.5

      Data sources

      1. Extract - fact id's that we pass values to live connection via action filters.
      2. Live connection

       

      Hello,

       

      We have a trends dashboard using our live connection that goes out and queries a large subset of months. It works, but it doesn't perform the best since it's looking back at 61 months. I'm trying to develop a feature that looks back 36 months from the month selected in the date filter. I am hoping this would shorten the query time and load the dashboard faster.

       

      Thus far, I've built out an option where I hide the 61 month graph, while showing the 36 month graph upon entry. I achieved this by adding a set of top 37 months to the graph showing upon entry and building a parameter to switch between the 36 month graph and the 60 month graph. This kind of works as it limits the the amount of months on initial entry, but the query is still running through all months of data in the backend, but only returning the top 36 months.

       

      The only sure shot way I have gotten this to work is by hard setting the date filter on the viz but that isn't a long term solution as it will require a monthly manual update to reset the filter.

       

      Is building a calculation to look back 36 months off of the same date filter selection possible while limiting the query in the back end? In the end, I'm trying to better control what we throw at the database to have it perform optimally.

       

      Month data type = Integer (not a datetime field)

       

      Unfortunately, I cannot provide a workbook with this data as it's very sensitive.

       

      Thank you in advance for your time!

        • 1. Re: How to show 'n' months back from month selection filter?
          Abhilash Sharma

          Hi Brian,

           

          Have you tried using a Data Source filter? This will limit the amount of data that is pulled from the database and will never exceed 36 months.

          Filter Data from Your Data Source

          Below is an example using Superstore sample data:

           

          Also, yes it is possible to create a calculated field and limit the data based on the date selection.

          Example:

          IF  DATEDIFF('month',[Yourdate],[ParameterDate]) < 37 AND

              DATEDIFF('month',[Yourdate],[ParameterDate]) > 0

                  THEN 'True'

                  ELSE 'False'

          END

           

          All you need to do is convert your integer month field to a date field and then you can use it in an above field.

           

          Please mark helpful and/or correct if it helped.

           

          ~AB

          • 2. Re: How to show 'n' months back from month selection filter?
            Brian Seefeldt

            Hi Abhilash,

             

            Thank you for your response, insight, and input!

             

            Data source filter is a great idea, however, still want to give the option to show full 60 months of data. The business wants to cut down initial load time while providing a full view on the trends.

             

            To your second solution, converting to a date returns some funky numbers for me. This would have to be a change that is made in our database level to change the data type. Also, this solution requires a parameter which wouldn't be dynamic and would require monthly manual updates.

             

            I've been trying to create calculations to evaluate the archive month id and then return a boolean value if greater than the max archive month offset by 37, but have been unsuccessful. Dynamic Parameters would really come in handy in this situation as we are looking for a streamlined automated updated process in terms of what months we show.

             

            LOOKUP([Archive Month ID - MAX],LAST()-37)

             

            IF [Archive Month ID]>[Archive Month Lookup]

            THEN 1

            ELSE 2

            END

             

             

            Your recommendations are correct for the specific purposes they are intended to do....just running into some data issues. I would still need to test if turning archive month to a date in the database solves the issue of the query scanning all archive months. My thinking is that it would since it would need to calculate the datediff on the values...I am running into thing using Last() and Max().

            • 3. Re: How to show 'n' months back from month selection filter?
              Abhilash Sharma

              Hi Brian,

               

              So you are saying that:

              1. You have 2 views which can be toggled using a parameter, one for 60 months the other for 36 months?

              2. Your month field is of integer type and cannot be converted to date type in tableau?

              3. And you want your data to load quickly since the query is taking too much time to load 60 months data?

               

              Proposed Solutions:

              1. How about creating an extract for the 60 months? That should help return values quickly. And you can setup refreshes for the extract on an incremental basis which will not harm the report performance.

               

              2. Using a lookup filter might help you in achieving the desired output but I dont think it will make things quicker. You can still try your current LAST() and MAX() combinations

               

              3. If you could by any chance convert the Months fields to a Date field it would make everything simpler in just one go. Also, by any chance can you tell what are some sample values for this field. Can you add year field to it in Tableau?

               

              ~AB

              • 4. Re: How to show 'n' months back from month selection filter?
                Brian Seefeldt

                Hey Abhilash,

                 

                Thanks again for your detailed responses, thoughts, and inputs!

                 

                 

                So you are saying that:

                1. You have 2 views which can be toggled using a parameter, one for 60 months the other for 36 months? -Correct, I tried this as a work around to see if I could bypass querying all months of data. It didn't work, so this doesn't need to be in place.

                2. Your month field is of integer type and cannot be converted to date type in tableau? -Correct again, when modified to date, it isn't readable, or make sense to a user.

                3. And you want your data to load quickly since the query is taking too much time to load 60 months data? Correct, which is why we are trying to limit the number of months.

                 

                As for the proposed solutions, #3 is the way to go and I've begun the discussion to get this in motion. One unknown is that if we change to date, and we now use the date functions, we still don't know if it will query all months, or just the 36 months we are looking for. This is proper way to approach the issue and we push as much processing down into the database level to lighten the load on tableau.We will also be exploring structuring the data as such in the database to reach back and deliver the last 36 months when one month is selected, but also having the option to query 60 months.

                 

                For #1, I believe our data is too big to create an extract. The query that fetches 60 months of data is about 100GB and over 1 billion rows/records.Not only is the data high volume, but high dimensional as well....and this is with aggregations in place. I'd love to be able to create an extract, but just don't think it's in the cards.

                 

                Solution #2 I just don't think is feasible. Even if I get the calculations to perform correctly, and it doesn't hit all months, the performance trade off won't be worth it as you mentioned.

                 

                Sample data for Archive Month in integer format is the following:

                 

                20180430

                20180331

                20180228

                20180131

                20171231

                etc.

                 

                Again, I can't thank you enough for your input and ideas. I believe we have some data prep work to do to gain this kind of functionality. Your analysis and troubleshooting has been spot on though!

                • 5. Re: How to show 'n' months back from month selection filter?
                  Abhilash Sharma

                  Hi Brian,

                   

                  Another idea came to my mind regarding tuning your performance load. We can have 2 data sources. 1 data source will be the primary data source which will have only 36 months data. This will be used in all the sheets and dashboards. And since you want to show 60 months as well, so for that single view/graph you can point to a new(duplicate) data source which has 60 months of data.

                   

                  Now when you assimilate everything on a dashboard you can put the 60 months view in a separate tab and all your 36 months together. This will at-least ensure that most of your views get loaded quickly. And in this scenario only the 60 months view should take extra time.

                   

                  Another thing that you can try to do is construct the date field. Since you already have 20180430 format. You can create 3 separate calculated fields for Year, Month and Day. Then append them into 1 string value and parse them into date format.

                   

                  Example:

                  Year: LEFT(ArchiveMonth, 4) = 2018

                  Day: RIGHT(ArchiveMonth, 2) = 30

                  Month: RIGHT(LEFT(ArchiveMonth, 6), 2) = 04

                   

                  Now append them together DATE(STR(Day +'/'+ Month+ '/' + Year))

                   

                  There might be syntactical errors in the above fields but I hope you do understand the reference I am trying to make.

                   

                  Let me know if either solution works for you. Also, please mark helpful and/or correct to what ever responses worked for you.

                   

                  ~AB

                  1 of 1 people found this helpful
                  • 6. Re: How to show 'n' months back from month selection filter?
                    Brian Seefeldt

                    Hey Abhilash,

                     

                    Thanks again for the input! I definitely need to look into multiple data sources in hopes we can make it work. Everywhere else in the workbook needs access to the full 60 months of data via the live connection, but limit to a single selection archive month, so can look 60 months back at that particular month. We build our marts/views for the full 60 months, but wondering if we could build a view with 36 months and that would server as our data source for the tab showing 60 months of plotted data....so thank you for that suggestion. I could then use a parameter to show 36 vs 60 and duplicate the worksheets pointing to the primary data source of 60 months.

                     

                    I've also tried constructing my own date, but I believe the live connection hinders that. I run into an error when building the month calc that I've researched and learned that it works within extract, but not live connection.

                     

                    Thanks!