4 Replies Latest reply on Mar 20, 2016 8:21 PM by Billy Santos

    Global Date Filter with multiple data source

    Billy Santos

      Hello everyone. So right now, I'm stuck in my project where I need to create a global date filter across multiple data sources.

       

      Here's the requirement:

      1 parameter: Report period            - will decide how many months to display

      1 parameter: Report End Month     - to filter the dashboard worksheets to display records based on how many months is selected in Report period.

       

      example: If I select Feb-2016 and select 3 from report period, the dashboard should display records from Dec-2015 to Feb-2016.

       

      Another thing is one data source has "Creation Date" as it's date field while the others uses "Date". So I cannot just create a date filter using a date field.

       

      This is very urgent and I hope you guys can help. I'm still new to this and I'm not very good at coding or creating calculations yet. I'd appreciate all the help. I've attached a sample of my workbook so you can take a closer look. I'm using version 9.1 of Tableau desktop.

       

      Thank you in advance.

        • 1. Re: Global Date Filter with multiple data source
          Takaaki Koseki

          Hi.

           

          I've changed the parameter [Report End Month] a little in order to calculate more easily.

          Here's my idea.

           

          GlobalDateFilter

          IF DATEDIFF('month',[Creation Date],
             IF [Report End Month]='Current' THEN TODAY()
             ELSE DATEPARSE('yyyy-MM',[Report End Month])
             END) < [Report Period]
          AND DATEDIFF('month',[Creation Date],
              IF [Report End Month]='Current' THEN TODAY()
              ELSE DATEPARSE('yyyy-MM',[Report End Month])
              END) >= 0
          THEN 'SHOW' ELSE 'HIDE' END
          

           

          I used two function mainly.

          DATEPARSE() - Convert [Report End Month] to date type

          DATEDIFF() - Calculate how many months djfference from [Report End Month]

           

          Thanks.

          • 2. Re: Global Date Filter with multiple data source
            Billy Santos

            Hi sir. This should be very close. I appreciate the help. However, is there a way to keep the original list in the Report End Month parameter?

             

            The filter should allow the user to freely select a month from Current to January....December because the records will keep updating in the coming months, years.

            • 3. Re: Global Date Filter with multiple data source
              Takaaki Koseki

              Hi.

               

              You mean you don't want [Report End Month] to include year part?

              And you want it to indicate the latest 12 month automatically?

               

              If so, the calculation will be a little bit more complex like this.

               

              IF DATEDIFF('month',[Creation Date],
                 IF [Report End Month]='Current' THEN TODAY()
                 ELSE DATEPARSE('yyyy-MM',
                      STR(DATEPART('year',TODAY())-IF DATEPART('month',TODAY())<INT([Report End Month]) THEN 1 ELSE 0 END)+'-'+[Report End Month])
                 END) < [Report Period]
              AND DATEDIFF('month',[Creation Date],
                  IF [Report End Month]='Current' THEN TODAY()
                  ELSE DATEPARSE('yyyy-MM',
                      STR(DATEPART('year',TODAY())-IF DATEPART('month',TODAY())<INT([Report End Month]) THEN 1 ELSE 0 END)+'-'+[Report End Month])
                  END) >= 0
              THEN 'SHOW' ELSE 'HIDE' END
              

               

              I added...

              STR(DATEPART('year',TODAY())-IF DATEPART('month',TODAY())<INT([Report End Month]) THEN 1 ELSE 0 END)+'-'+

              and changed [Report End Month] into ['Current','01','02',...,'12'].

               

              Thanks.

              • 4. Re: Global Date Filter with multiple data source
                Billy Santos

                Yes. The Report End Month should not include any year part in the selection because when user selects a month, it should also retrieve the current year.

                By default, "Current" (March) is selected.

                 

                As for the Report Period, by default it should display the last 12 months but user is allowed to select from 1 to 18 months to display.

                 

                Thanks a lot.