4 Replies Latest reply on Jan 9, 2017 11:13 AM by Shivanankari Shivanna

    Display MIN and MAX Date Range

    philip.thornton

      I have dashboards where I display the start/end dates for the data range selected in a filter.

      <MIN(Date)> - <MAX(Date)>

      This allows end users to quickly see the date range. The issue is if there aren't an rows for the start of end dates, the header looks incorrect.

       

      Here's an example.

         An end user adjusts the date range filter start date 1/1/2016 and end date 12/31/2016.

         The data viewed doesn't have entries for January 1st-3rd.

         The title shows 1/4/2016 - 12/31/2016.

       

      Not ideal.

       

      Is there any hidden voodoo that will display the exact dates entered in the date range filter and not reply on what data was found?

      [start_date_range] - [end_date_range]

       

      Philip

        • 1. Re: Display MIN and MAX Date Range
          David Li

          Hi Philip, if you know that your date filters will always have some kind of restriction, e.g. that they'll always be at the start and end of months, then you can use functions like DATETRUNC to adjust for that. Otherwise, you could use parameters to control the dates and just display the values of the parameters.

          • 2. Re: Display MIN and MAX Date Range
            Joshua Milligan

            Philip,

             

            I cannot think of a way to do this short of manipulating the data so that every date is included.  As you noted, you can get the min and max values that are in the data post filter (and could get the min/max pre-filter with a fixed LOD), but there's no way to display the values of the filter itself

             

            I wrote that and then I thought of a way.  What if you created another data source with all the dates (with every day included) and then used that as the display for what is selected?  The key is to apply the date filter from your original view to All Related Data Sources (cross database filters available in Tableau 10) so it filters not only the view you have now but also the one that shows the display of Min and Max.  Since the Min and Max in the full data set always include the dates for the range of the filter, you'll get a display that exactly matches.

             

             

            This is the original view that has the Date filter as a range (note that 1/1/2016 is selected, but the data viz starts at 1/4).  The filter has been set to apply to All Relevant Data Sources.

             

             

            This view is built from the full Dates dataset and gets the filter applied from your main data source.  Since it has every date, it can display the selection correctly:

             

            Put them together on a dashboard, and you've got what you want:

             

            Hope that helps!

            Joshua

            2 of 2 people found this helpful
            • 3. Re: Display MIN and MAX Date Range
              philip.thornton

              A little discouraged because I have several dashboards and was hoping for a quicker way to get around it. That said, what you suggested will work. I think I'll go with another data extract for the handful of dashboards that don't have data on every day of the year.

               

              Thanks so much for your quick feedback.

               

              Philip

              • 4. Re: Display MIN and MAX Date Range
                Shivanankari Shivanna

                I would suggest to create two parameters, one for MIN Date and one for  MAX Date. Then you can write a calculated field like,

                [Date]>=[Parameter Min Date] AND [Date] <= [Parameter Max Date] . Use the Parameter to control your view. with the calculated field as a filter.