4 Replies Latest reply on Nov 30, 2017 5:19 PM by Tai Vo

    Date Generator

    Tai Vo

      Hello Tableau Friends,

       

      I have multiple dates in my dataset, and I'd like to generate a reference date that links across all the dates in my dataset. I'd like to use this reference date as the master calendar date filter. So, how do I generate reference master calendar date based on the min(dates of all different dates in my dataset) and the current date? I'd like the date calculation to be dynamically updated every time I refresh the data.

       

      Has someone solve this problem before?

       

      Thanks!

        • 1. Re: Date Generator
          Nicole Le Prohn

          It isn't clear what you want to do. Do you want the new date field to = the refresh date of the data? Or do you want it to = the first date (earliest date) of all date measures? I am unclear how you want the new date field will link all the dates.

          • 2. Re: Date Generator
            Tai Vo

            Let me clarify with an example. Say in my dataset I have 6 dates as follows:

             

            1. Planned Start Date

            2. Target Start Date

            3. Actual Start Date

            4. Planned End Date

            5. Target End Date

            6. Actual End Date

             

            Now say the user wants to view any activity that occurs between 1/1/2017 and 3/10/2017. Which date would I select for date filter? I can't simply select any of the 6 dates to use as quick filter because that would represent an incomplete view.

             

            In order to use just one common date as filter, I need to generate a new date that contains "all" possible dates across all 6 date fields. To do this I was thinking of getting the min date and the max date across all 6 date fields in the dataset and convert them to an integer representation of date.

             

            Say min date is 1/1/2008 which corresponds to the integer value of 39448.

            max date is the current date, which is 11/30/2017 and that corresponds to 43069. So now I need Tableau to loop through the range of min and max date and generate a list of integers between 39448 and 43069 to cover all possible dates in the dataset. So for the quick filter, I'd use that newly generated date as the reference date filter to drive my dashboard.

             

            How to do this in Tableau?

            • 3. Re: Date Generator
              Tom W

              Create two parameters; one for the start date, one for the end date.

               

              Then you need to create a calculated field which will act as your filter i.e.

              ([date1] >= [parameterstart] and [date1] <= [parameterend])

              or

              ([date2] >= [parameterstart] and [date2] <= [parameterend])

              or

              .....

               

              Finish that formula for all possible date fields.

              Drag it onto your filter shelf and set it to True.

              • 4. Re: Date Generator
                Tai Vo

                Thank you, Tom! That works:)

                 

                Tai