3 Replies Latest reply on Sep 10, 2017 9:18 AM by Michael Ye

    Date Filter Across Multiple Date Columns (1 Data Source)

    Luigi Cicciari

      Hello,

       

      I've been playing with this one for a while and I can't figure it out. I have a data source that includes multiple dates (e.g. a sale date, refund date, survey date, and etc). I'd like to be able to find all records for a certain day with one filter.

       

      Attached is a sample of what the data looks like.

       

      thank you,

      Luigi

        • 1. Re: Date Filter Across Multiple Date Columns (1 Data Source)
          Ray Givler

          Hi Luigi,

          1.Create a parameter [Date Parameter] for your target date. Set it up to allow the date to be typed in.

          2.Create a calculated field like "date check":

          if [Sale Date] = [Date Parameter] or

             [Revenue Date] = [Date Parameter] or

             [Refund Date] = [Date Parameter] or

             [Survey Date] = [Date Parameter] or

             [Regisration Date] = [Date Parameter]

          then 'Y' else 'N' END

          3. Drag this calculated field to filters and filter to Y.

          • 2. Re: Date Filter Across Multiple Date Columns (1 Data Source)
            Simon Runc

            hi Luigi,

             

            This can be a tough problem, as that data structure is horrible (from "doing work on the data" perspective)...and while it does have some capability, Tableau is a viz tool first, with a bit of data manipulation/re-shaping thrown in! I would look to get your data in a different format, if possible.

             

            However there is a way, and probably only if you have these 5 fields (else it becomes too laborious), to force the data into the "right" shape.

             

            First I Unioned your data to itself 5 times!

             

             

            Then using the Table Name I created a single date instance in a formula

             

            [Combined Date]

            CASE [Table Name]

                WHEN 'Sheet1' THEN [Sale Date]

                WHEN 'Sheet11' THEN [Revenue Date]

                WHEN 'Sheet12' THEN [Refund Date]

                WHEN 'Sheet13' THEN [Survey Date]

                WHEN 'Sheet14' THEN [Registration Date]

            END

             

            and we also have to create a new version of each measure...

            [Sales To Use]

            IIF([Table Name] = 'Sheet1',[Sale],NULL)

             

            [Revenue To Use]

            IIF([Table Name] = 'Sheet11',[Revenue],NULL)

             

            and so on...

             

            Now the data is 5 times it's usual size, but if you extract it into a TDE you'll be fine with 5-10 million rows, and barley notice (especially if you Optimize the extract after you've written the calculations for Combined Date and Measure To Use...Tableau should materialise those values, so it don't have to calculate them each time)

             

            Another option would be to use CustomSQL to reshape the data, or if you only want to see all the activity on a particular day, you could create a parameter of type = date, and set to any. You could then write an IF statement, which included a check on each of your 5 date fields.

             

            Hope that helps.

            2 of 2 people found this helpful
            • 3. Re: Date Filter Across Multiple Date Columns (1 Data Source)
              Michael Ye

              Hi Luigi,

               

              Please see the twbx file attached.

               

              For the data source, I have added two more lines to make more dates. What you need to do is:

               

              (1) Build a parameter: Date Search.  Added members through "added from filed" and added all the five date: sales date, revenue date, registration date, refund date, survey date.

               

              (2) Make a calculated field: Search

               

              CONTAINS(STR([Sale Date]),STR([date search]))

              OR

              CONTAINS(STR([Refund Date]),STR([date search]))

              OR

              CONTAINS(STR([Registration Date]),STR([date search]))

              OR

              CONTAINS(STR([Survey Date]),STR([date search]))

              OR

              CONTAINS(STR([Revenue Date]),STR([date search]))

               

              (3) Put the calculated field to filter, select 'True"

              (4) Right click parameter, select "show parameter control".

              (5) Done.

               

              When you select a date, all records will be shown for a certain day you select with the parameter.

               

              Thanks,

               

              Michael

              3 of 3 people found this helpful