2 Replies Latest reply on Feb 19, 2014 12:40 PM by Kat von

    Filter with different date parameters

    Kat von

      I would like to display different data based on the date parameter I choose. If I choose day, I would like to take the last 8 weeks (from yesterday). If I choose week, I would like to choose the last 8 weeks based on last week, etc for month.

       

      How would I do so?

        • 1. Re: Filter with different date parameters
          Tracy Rodgers

          Hi Kat,

           

          By creating a calculated field similar to the following should put you in the right direction:

           

          case [Choose Date Level]

          when 'Daily' then (if ( max([Date]))>=dateadd('day', -8, window_max(max([Date]))) and max([Date])<=window_max(max([Date])) then max([Date])end)

          when 'Weekly' then (if ( max([Date]))>=dateadd('week', -8, window_max(max([Date]))) and max([Date])<=window_max(max([Date])) then (max([Date]))end)

          when 'Yearly' then (if ( max([Date]))>=dateadd('year', -8, window_max(max([Date])))and max([Date])<=window_max(max([Date]))then (max([Date])) end)

          when 'Quarterly' then (if ( max([Date]))>=dateadd('quarter', -8, window_max(max([Date])))and max([Date])<=window_max(max([Date]))then (max([Date])) end)

          when 'Monthly' then (if ( max([Date]))>=dateadd('month', -8, window_max(max([Date]))) and max([Date])<=window_max(max([Date]))then (max([Date]))end )

          else null

          end

           

          Place this on the columns shelf in place of the Choose Date Level Calc (and place the Choose Date Level Calc on the level of detail shelf).

           

          Note: this data set only have the first day of the month in the date fields (so daily doesn't work as you might expect).

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Filter with different date parameters
            Kat von

            Thanks Tracy Rodgers.

             

            What I need for the daily is not actually daily, but certain days of week (ie, the last 8 Wednesdays). I was using this

            DATEPART('weekday', Date)=DATEPART('weekday', Today()-1), but am not sure how to properly integrate that into data. Also...I want last full week., last full month to start out the past 8 week calculation.