4 Replies Latest reply on Apr 30, 2018 2:20 PM by Tim Cady

    Creating a date filter parameter

    Tim Cady

      Hi all,

       

      I recently saw a dashboard that had a filter, seemingly a parameter, that would allow the user to change the timeframe of the entire dashboard without using any an actual date filters. This parameter had options like year-to-date, month-to-date, last week, etc.

       

      I'd assume I'd make something like this with a parameter list but I don't know how I would create the date range for each list item.

       

      Anyone have ideas on how this could be done?

        • 1. Re: Creating a date filter parameter
          Marc-Anthony Di Biase

          Hi Tim,

           

          I believe you're referring to the relative date filter - you can learn more about it here: Quick Start: Relative Date Filters.

           

          Let me know if this is what you were looking for!

          • 2. Re: Creating a date filter parameter
            Tim Cady

            Hi Marc,

             

            Thanks for the quick response. That's not quite what I was looking for.

             

            I've provided a screenshot of the filter below. I'm unable to access the workbook in question because I only have a video of it.

             

            • 3. Re: Creating a date filter parameter
              Marc-Anthony Di Biase

              An interesting approach to relative date filters - I'm assuming you would only want some of the options rather than all which is why you would want the drop down.

               

              This is easily achievable with some calculated fields. Following the screenshot you posted, you would need three calculated fields that would correspond to each of the available options. I'm going to assume your data uses a date field which is called [Date].

               

              Calc Field 1: 30 Filter Switch (Assuming this means last 30 days)

              IF(DATEDIFF('day', today(), [date]) < 30) THEN TRUE ELSE FALSE END

               

              Calc Field 2: MTD Filter Switch

              IF(Month([Date]) = Month(today()) AND year([date]) = year(today())) THEN TRUE ELSE FALSE END

               

              Calc Field 3: YTD Filter Switch

              IF(year([date]) = year(today())) THEN TRUE ELSE FALSE END

               

              Next, create a parameter with your three values (in this case). We'll call it Selected Date Filter and the values will be 30, MTD, YTD

               

              Finally, you would tie together these three fields with a case statement - which will act as your dynamic filter.

              CASE [Selected Date Filter]

                   WHEN '30' THEN [30 Filter Switch]

                   WHEN 'MTD' THEN [MTD Filter Switch]

                   WHEN 'YTD' THEN [YTD Filter Switch]

              END

               

              Let me know if this is what you're looking for and if you have any other outstanding questions.

              1 of 1 people found this helpful
              • 4. Re: Creating a date filter parameter
                Tim Cady

                Thank you, this looks like it worked.