3 Replies Latest reply on Feb 21, 2012 4:53 AM by Tim Hill

    Problem creating a custom date filter

    Tim Hill

      I'm working on a dashboard that has a requirement for set date ranges to be available for the user to select such as "Last 3 months", "Last 6 months", etc.  These ranges need to be able to span years and need to be updated without me having to republish the dashboard which means it needs to be dynamic.  I tried using the built in date filters of the slider and the relative months but the slider has a bug with the start that makes it not work correctly and the relative month quick filter gives the user way more choices then we want them to see.  So I decided to use a solution based on other threads I've read in the forums where I have a parameter with the filter options in it that I want and then a calculated field that figures out the time period like this.

       

      CASE [Select Date Range]

      WHEN "All" THEN MIN(DATETRUNC('month',[CALENDAR_DATE]))

      WHEN "3 months" THEN DATEADD('month',-3,TODAY())

      WHEN "6 months" THEN DATEADD('month',-6,TODAY())

      WHEN "1 year" THEN DATEADD('month',-12,TODAY())

      END

       

      and another calculated field that checks if the date is greater than or equal to what was chosen and filter on that boolean value.

       

      DATETRUNC('month',[CALENDAR_DATE])>=DATETRUNC('month',[Selected Start Date])

       

      This works perfectly for the 3 month, 6 month, and 1 year filter, which is good because it's just calculations from there for other periods based on weeks or another time frame; however, I can't get it to work for when the user wants to see all data.  Since data is added constantly it has to dynamically grab the earliest date we have and that is where I'm stuck.  I thought what I did above would work but then it can't do the comparison in the boolean calculated field because you can't mix aggregate and non aggregate fields.  I have tried a few methods of determing the first date we have so that "All" will work but can't figure it out.  Hopefully I'm just being blind and there is an easy fix to this one.  All help is appreciated.

        • 1. Re: Problem creating a custom date filter
          Tim Hill

          For anyone that may have been curious about this issue I did find a solution.  The solution was to make the case for all the same as what I'm checking for.  That way the boolean will always be true which is what I ultimately want for that selection.  The date range filter is now working correctly as shown below.

           

          CASE [Select Date Range]

          WHEN "All" THEN DATETRUNC('month',[CALENDAR_DATE])

          WHEN "3 months" THEN DATEADD('month',-3,TODAY())

          WHEN "6 months" THEN DATEADD('month',-6,TODAY())

          WHEN "1 year" THEN DATEADD('month',-12,TODAY())

          END

          • 2. Re: Problem creating a custom date filter
            Shawn Wallwork

            Hi Tim, thanks for the update, I was just going to suggest replacing your first line with:

             

            WHEN "All" THEN DATEADD('month',-1200,TODAY())

             

            But your solution works better.

             

            --Shawn

            • 3. Re: Problem creating a custom date filter
              Tim Hill

              Ok, so my solution works fine with live data but I'm having a problem using this with data extracts.  What is happening is we want the default view to be "Last 6 months" but when an extract is created with the filter sets it seems to only pull in that amount of data so the "Last year" and "All" do not show anything more.  I thought that the filter only controlled what was disaplayed and not the data in the extract.  Does anyone understand why this is happening and how to correct this issue?  We are loading new data on a daily basis so the extract is generated nightly.