4 Replies Latest reply on Jul 9, 2017 9:36 PM by Russel Quan

    Single filter for multiple dates

    Marcus Tolman

      I have a data set that is covering a multi-step process and therefore I have multiple date fields. I would like to have one date filter that applies to all 5 date fields but am not sure how to accomplish this. I would like to have a filter where the user could select "Q1" or "Q2" for example and see all activity that occurred in that quarter across each step.

       

      From digging around on here it looks like the secret is to set a date parameter and then create a calculated field that you then use as a filter. The only issue is most of the solutions I've seen on here are for one date field only, and I can't seem to figure out the calculated field piece for multiple dates. I feel like there is probably an easy solution that I can't figure out.

       

      I have attached an example workbook similar to the structure of my original workbook. Any help or ideas would be greatly appreciated.

        • 1. Re: Single filter for multiple dates
          Alex Kerin

          Looking at the data - do you ever have more than one date in a row? - At the moment you have A Date, rest are null, then rows of B date, then rest are Null?

           

          If no, then I would create a single Date dimension:

           

          date(zn(float([A Date]))+zn(float([B Date]))+zn(float([C Date]))+zn(float([D Date]))+zn(float([E Date])))

           

          and use this to filter

          • 2. Re: Single filter for multiple dates
            Marcus Tolman

            THANK YOU Alex! This worked like a charm. I do not have multiple dates on a single row, so your formula worked great. I was pretty sure it was just my lack of programming knowledge with regard to calculated fields that was holding me back.

             

            Just for my own info & knowledge, could you explain what each of these functions is doing in the formula and why it works?

            • 3. Re: Single filter for multiple dates
              Alex Kerin

              Starting with dates - in all systems they are represented as some sort of number from from an epoch date. In Excel's way of doing it, today is the 41,381st day past their epoch.

               

              So given all of your other dates are nulls, we need to change them to type float from a number (kind of irrelevant as they are nulls anyway), then NULL in Tableau is really that - not a number, so we use zn() to change nulls to 0, or keep the date as a float if it's not null. Add them all up: 41,308+0+0+0+0 and change that back to a date()

              1 of 1 people found this helpful
              • 4. Re: Single filter for multiple dates
                Russel Quan

                Hello Alex,

                Based on your expertise in the previous example, do you have any thoughts about the date puzzle I posted at this link?

                Thanks in advance,

                Russel

                 

                One Date Filter controls Two Date Columns