12 Replies Latest reply on Feb 14, 2020 11:49 AM by Derek Paggioli

    Filtering by last N of selected date level

    Derek Paggioli

      Hello all!

       

      Hopefully, I can get a little assistance here, as I've been banging my head up against calculated field after calculated field I've found in various searches to try and get this working, and I may just be trying to do something that's not entirely possible.

       

      In the attached workbook, I am currently using the Date Level Selector Parameter (from Tableau's help topic regarding the Date Selector w/ Formatting) to filter dynamically between Year, Quarter, Month, and Week.

       

      Everything there is working great, however, as the data set grows, the end-user would eventually like to be able to further filter by the last N weeks/months/quarters on a Dashboard. Basically, last N of the date granularity chosen in the Date Level Selector parameter.

       

      I've tried a number of different sets of parameters and calculated fields using LAST, MIN/MAX, etc. to try and achieve this, but nothing seems to be working and I feel like I'm missing a crucial bit of information (either that I'm not targeting things in the calculated fields appropriately or that it simply cannot be done!)

       

      Any help would be most appreciated!

       

      Thanks!

        • 1. Re: Filtering by last N of selected date level
          Rohit Yeruva

          Hey Derek,

           

          If I understood right, you have data until Jan 2020 and want to show last 2 months of data(Dec 2019 & Jan 2020) and about a month later, when it's Feb 2020, you want to show Jan 2020 & Feb 2020 data? If that's right, you are lucky! Tableau is introducing dynamic parameters in Tableau 2020.1 and this should be possible then!!

           

          Coming Soon: New features in Tableau

           

          Regards,

          Rohit

          • 2. Re: Filtering by last N of selected date level
            Jim Dehner

            Hi

            As you know that could be pretty difficult - I have something on how to do a Top N on a customer hierarchy drill down - the user would drill in or out of the hierarchy and the top (or in your case the bottom ) would both filter and sort

            it avoids the parameter but is a different UI

             

            see    See it your way: Top N

            it is the last example

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Filtering by last N of selected date level
              Derek Paggioli

              Hi Rohit,

               

              Thank you for the response.

               

              I currently have only data starting in Jan 2020 and each week moving forward will add more data, as we are measuring some employee KPIs.

               

              As time goes on, the end-user would like to be able to filter by either the year/quarter/month/week and then further filter that view by a number of last "whatever is selected" from the current date.

               

              So, for instance, say they select "Week" from the "Date Level Selector" parameter, right now it would show the entire data set on the viz broken down by week.

               

              They would like to then have the ability to modify another parameter, which I have named "Choose Period", to indicate the number of weeks to see, akin to the out-of-the-box Relative Date filter.

               

              However, since the Date Selector on my columns is a string that sorts the viz by the chosen date granularity, I cannot use it as a Relative Date filter and adding a Relative Date filter by my "Week of" Dimension does not update the viz by the Date Selector parameter.

               

              Continuing with my example above, say they have chosen "Week" in the "Date Level Selector" and then type a "4" in the "Choose Period" parameter, it would show this week and the previous 3 weeks. If they type a "7", it would show this week and the previous 6 weeks (like the existing Relative Date filter).

               

              Then, if they switch to the "Month" option of the "Date Level Selector" parameter and leave the "7" in the "Choose Period" parameter, it would update the viz to show the monthly data for the current and past 6 months (where data is available).

               

              I hope this makes a little more sense, as I'm not sure that upcoming Dynamic Parameters feature will actually address this issue, since the parameter for the chosen period of time is user-defined already.

              • 4. Re: Filtering by last N of selected date level
                Derek Paggioli

                Hi Jim,

                 

                Thank you for the response.

                 

                I'm not entirely sure how I would apply your example to my data, but I have written this response to Rohit, as it seems like my question may have been misinterpreted. Maybe this can help you help me understand how I might apply your suggestion to my situation:

                 

                I currently have only data starting in Jan 2020 and each week moving forward will add more data, as we are measuring some employee KPIs.

                 

                As time goes on, the end-user would like to be able to filter by either the year/quarter/month/week and then further filter that view by a number of last "whatever is selected" from the current date.

                 

                So, for instance, say they select "Week" from the "Date Level Selector" parameter, right now it would show the entire data set on the viz broken down by week.

                 

                They would like to then have the ability to modify another parameter, which I have named "Choose Period", to indicate the number of weeks to see, akin to the out-of-the-box Relative Date filter.

                 

                However, since the Date Selector on my columns is a string that sorts the viz by the chosen date granularity, I cannot use it as a Relative Date filter and adding a Relative Date filter by my "Week of" Dimension does not update the viz by the Date Selector parameter.

                 

                Continuing with my example above, say they have chosen "Week" in the "Date Level Selector" and then type a "4" in the "Choose Period" parameter, it would show this week and the previous 3 weeks. If they type a "7", it would show this week and the previous 6 weeks (like the existing Relative Date filter).

                 

                Then, if they switch to the "Month" option of the "Date Level Selector" parameter and leave the "7" in the "Choose Period" parameter, it would update the viz to show the monthly data for the current and past 6 months (where data is available).

                 

                I hope this makes a little more sense, as I'm not sure that upcoming Dynamic Parameters feature will actually address this issue, since the parameter for the chosen period of time is user-defined already.

                 

                Thanks again!

                • 5. Re: Filtering by last N of selected date level
                  Jim Dehner

                  Thanks for the clarification - I'm going to make a suggestion that you convert the string date to a real date - (your user will never see it)  then use the real dates to do the date calculations and create your vis with labels that meets the user expectations - dates are a special class of dimension that self sequences, sort and allows the use of date functions -

                   

                  by using strings you are placed in the position of trying to do all that  you will be rewriting that with strings and conditional statements

                   

                  Jim

                  • 6. Re: Filtering by last N of selected date level
                    Rohit Yeruva

                    Hey Derek,

                     

                    I understand your problem now. I have a solution for you. Will post it shortly.


                    Regards,

                    Rohit

                    • 7. Re: Filtering by last N of selected date level
                      Rohit Yeruva

                      Hey Derek,

                       

                      Here you go!

                       

                      Assumptions: From your data, I've assumed that you have weekly data with week starting on a Mondays.

                       

                      Steps:

                      1. Create a calculated field that anchors your latest date which is used to determine how far into the history do we have to go (which is given by your second parameter).

                       

                      2. Create a calculated field that feeds off of your first parameter and adjusts the dates. Since your weeks start on Monday, I made appropriate adjustments.

                       

                      3. Now we create a Boolean calculated field that filters in the chosen period:

                      4. Add the above Boolean field to your filters and select True. Now you should have what you need. Adjust the date formats as you like.

                       

                      Finally, as Jim Dehner mentioned, using strings is not ideal as you can observe from your original view, the dates are not in order. That's false information.

                       

                      I've attached your dashboard with the changes. Also attached superstore workbook with similar example for additional reference so that you can check for quarters and years.

                       

                      Let me know if this helps!

                       

                      Regards,

                      Rohit

                      2 of 2 people found this helpful
                      • 8. Re: Filtering by last N of selected date level
                        Derek Paggioli

                        Hi Rohit,

                         

                        Thank you for the reply and the solution is so close to where I was hoping to be that I'm thinking of just listing this as the correct answer.

                         

                        However, and I may have to accept this as an inevitable loss in formatting, the reason I was using strings was to format the Y-axis to show the associated date formatting for the selected date levels.

                         

                        For instance, in my original example, you will see that when the user selects the individual options in the Date Level Selector, the formatting of the Y-axis updates as follows:

                         

                        • Week: Month (numeric date), (numeric year)
                        • Month: Month, (numeric year)
                        • Quarter: Q+(numeric quarter), (numeric year)
                        • Year: (numeric year)

                         

                        I used the solution found here: https://kb.tableau.com/articles/howto/Dynamically-Changing-the-Date-Level (in the Additional Information section and twbx attached to the how to article) to do that, but maybe you can see another way?

                         

                        Thanks again!

                        • 9. Re: Filtering by last N of selected date level
                          Rohit Yeruva

                          Hey Derek,

                           

                          Sure, are you open to use a dashboard? Then yes!

                           

                          1. Create a new sheet with the new date dimension that we created and your string field on your columns. Hide the date header so that only your string field values are displayed.

                          2. Now move both your sheets onto a dashboard. Adjust the sizes, use blank containers to align and remove the axis from your original view and you should have what you need.

                           

                           

                          Attached the workbook with these changes. Let me know if this would suffice.

                           

                          Regards,

                          Rohit

                          2 of 2 people found this helpful
                          • 10. Re: Filtering by last N of selected date level
                            Derek Paggioli

                            Hi Rohit,

                             

                            I was 100% hoping to use this in a Dashboard, so this is absolutely perfect!

                             

                            I marked your initial answer as correct, since it solved the issue in the topic title, but I wish I could mark both of them as correct.

                             

                            Many, many thanks, this is beautiful and exactly what I was hoping to do.

                             

                            Thanks again!

                            1 of 1 people found this helpful
                            • 11. Re: Filtering by last N of selected date level
                              Rohit Yeruva

                              Haha! Thanks Derek. You can't mark multiple correct answers, but you can surely mark all of them helpful!

                              2 of 2 people found this helpful