7 Replies Latest reply on Aug 23, 2018 2:58 AM by Okechukwu Ossai

    Using one of two available date filters

    Nimit Bhardwaj

      I have data where I have the number of requests received over the past 6 months. I want to show this data on a dashboard with two available filters for the viewer.

      The viewer should be able to use one of these two filters:

       

      1) Data displayed for the last N weeks.

       

      OR

       

      2) Data displayed for a particular month.

       

      I am able to achieve these two on separate sheets using parameters for number 1 and filter for number 2. How do I make them work on the same sheet?

      I want the default to be set as last 4 weeks.

        • 1. Re: Using one of two available date filters
          Okechukwu Ossai

          Hi Nimit,

           

          By adding the parameter and filter to the same worksheet, do you want to see the Last N week of the filtered month? If yes, add both parameter and Month filter to the worksheet. Right click on the Month field on the filter shelf and select 'Add to Context'.

           

          Leave your parameter selection as 4 before publishing to the server. This will ensure it Last 4 weeks show as the default selection.

           

          Hope this helps.

          Ossai

          • 2. Re: Using one of two available date filters
            Nimit Bhardwaj

            Thanks for your response.

             

            What I need is basically an option between filtering by last N weeks OR by a particular month, i.e. by default I should be able to see data for the last N weeks but if I want to see the data for a particular month, then the last N weeks filter should turn off. Similarly, the month filter should turn off if I want to switch to last N weeks again.

            • 3. Re: Using one of two available date filters
              Okechukwu Ossai

              In that case you need a high level parameter, for example, called [Display]

              It should have Last N Week and Month as Options. A user will have to select that first before selecting the appropriate last n week parameter or month filter accordingly.

              • 4. Re: Using one of two available date filters
                Nimit Bhardwaj

                Okay, that makes sense. How do I go about creating that?

                • 5. Re: Using one of two available date filters
                  Okechukwu Ossai

                  Can you post a dummy packaged workbook?

                  • 6. Re: Using one of two available date filters
                    Nimit Bhardwaj

                    The attached workbook is essentially what I need to work with. I've added the month and last N weeks filters already.

                    • 7. Re: Using one of two available date filters
                      Okechukwu Ossai

                      Hi Nimit,

                       

                      Your requirement is multi-layered and this will affect the level of complexity of the solution. You want to each display selection to shut off the other option. So, If Last N Weeks is selected, then Month will become inactive. You can only achieve this in one worksheet by using 3 parameters, Display, Last N weeks and Month.

                       

                      However, if you want to Month to be a filter and not a parameter, then you will have to build two separate worksheets, one each for Last N week and Month options respectively. ameter logic to swap them in your dashboard.

                       

                      Option 1: Use 3 parameters and keep everything in one worksheet

                       

                      Step 1: Create a parameter [Display]

                       

                      Step 2: Create parameter [Last N weeks]

                      You've already done this.

                       

                      Step 3: Create parameter [Month]

                       

                      Step 4: Create calculated field [Date to Show]

                      IF [Date] =

                      (IF [Display] = 'Last N Weeks' THEN

                          IF DATEDIFF('week', [Date], {FIXED: MAX([Date])}) < [Last N weeks] THEN [Date]

                          END

                      ELSE

                          IF YEAR([Date]) = YEAR({FIXED: MAX([Date])}) AND DATENAME('month', [Date]) = [Month] THEN [Date]

                          END

                      END)

                      THEN 'Show' ELSE 'Hide' END

                      Add this to the filter shelf and select 'Show'. Note the date axis of your chart in this option is static. When Month is elected it will show the month of the latest year in your database. The month parameter is single selection only.

                       

                       

                      Option 2: Use a Month filter 2 parameters.

                      You'll need 2 separate worksheets for the charts and use sheet swapping technique to display each on your dashboard. This option gives the opportunity to have independent date axis as shown in the screenshots below.

                       

                      Step 1: Create calculated field [Date to Show (Option 2)]

                      IF [Date] =

                      (IF [Display] = 'Last N Weeks' THEN

                          IF DATEDIFF('week', [Date], {FIXED: MAX([Date])}) < [Last N weeks] THEN [Date]

                          END

                      ELSE [Date]

                      END)

                      THEN 'Show' ELSE 'Hide' END

                      Add this as a filter to each worksheet and select 'Show'.

                       

                      Step 2: Create calculated field [Select a View]

                      [Display]

                      Add this to the filter shelf and follow the instructions in the links below to swap between sheets on your dashboard. I've added some screenshots also.

                      https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#dashboards_sheet_selector.html

                       

                       

                       

                       

                      Hope this helps.

                      Ossai

                      1 of 1 people found this helpful