2 Replies Latest reply on Nov 1, 2016 7:25 AM by Christina Morgan

    Custom "This Week" Filter

    Christina Morgan

      I'm creating a dashboard for a team whose week begins at noon on Wednesday. I'm trying to build a calculated field that includes only entries back to the previous Wednesday at noon. How would I build this calc? I've been wrestling with DATEDIFF, DATETRUNC, DATEPART and NOW, but can't quite get there.

        • 1. Re: Custom "This Week" Filter
          Joe Oppelt

          I would start with a calc like this:




          On Monday, this returns a value of 2, for example.


          So if that calc is > 4, then maybe do a datediff of some sort from TODAY() backward subtracting 4 from the calc value.  And if it's less than 4 you want to go back 7 - ([calc value]+4).  Something like that.  And you'll have to do a MAKEDATETIME() to insert theNOON time in there:


          MAKEDATETIME(date, time)

          Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime. This function is available only for MySQL connections.


          MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#      

          MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#



          Basically you want to create a date range by building your starting date and ending with TODAY().


          Then have a calc that looks like this:



          IF [date] >= [start date calc] and [date] <= today() then 1 else 0 end


          (The part about <=today() may be moot if you have no dates in the future.)


          Put that on your filter shelf and select for value = 1.

          • 2. Re: Custom "This Week" Filter
            Christina Morgan

            Thanks, Joe. I hadn't used MAKEDATETIME before. That's exactly what I needed.


            I have three calculated fields:


            Now's weekday number:



            Last Wednesday at Noon:

            if [Now's Weekday number] = 1 then MAKEDATETIME(today() - 4, #12:00:00#)

            elseif [Now's Weekday number] = 2 then MAKEDATETIME(today() - 5, #12:00:00#)

            elseif [Now's Weekday number] = 3 then MAKEDATETIME(today() - 6, #12:00:00#)

            elseif [Now's Weekday number] = 4 and datepart('hour', now()) < 12 then MAKEDATETIME(today() -7, #12:00:00#)

            elseif [Now's Weekday number] = 4 and datepart('hour', now()) >= 12 then MAKEDATETIME(today(), #12:00:00#)

            elseif [Now's Weekday number] = 5 then MAKEDATETIME(today() - 1, #12:00:00#)

            elseif [Now's Weekday number] = 6 then MAKEDATETIME(today() - 2, #12:00:00#)

            elseif [Now's Weekday number] = 7 then MAKEDATETIME(today() - 3, #12:00:00#)



            Start Time > Last Wednesday at Noon (this one I use as the filter):

            [Start Time] > [Last Wednesday at Noon]


            Thanks again Joe!