5 Replies Latest reply on Jul 30, 2018 6:45 AM by Stephen Groff

    Date Parameter - Show Last Friday

    Stephen Groff

      Hi All,

       

      I know of several ways of creating date parameters, but I'm running into a problem with what people want in the dashboards I'm creating and what I'm able to do.

       

      I need to create a parameter that allows any user to view "Last Friday" no matter what the current date is.

       

      Anyone know how to accomplish this?

       

      IF [Date Parameter] = "Last Friday" THEN [Date].....

       

      Thanks in advance.

       

      Steve

        • 1. Re: Date Parameter - Show Last Friday
          Okechukwu Ossai

          Hi Steve,

           

          It depends on how you want to use the results. Do you want the last Friday of every month or the last Friday of the entire dataset? I assume the latter. You will need to calculate weekdays and then use an LOD to get the MAX Friday date. I'll split the code into various chunks for clarity.

           

          [Last Friday (Fixed)]

          {FIXED DATENAME('weekday', [Date]):

          MAX(IF DATENAME('weekday', [Date]) = 'Friday' THEN [Date] END)}

          [Last Friday Date]

          IF [Last Friday (Fixed)] = [Date] THEN [Date] END

          Finally, you can link the calculated field to date parameter

           

          [Last Friday]

          IF [Date Parameter] = "Last Friday" THEN [Last Friday Date] END

          This will return the last Friday date and null values. Add [Last Friday] to the filter shelf and exclude null.

           

          Hope this helps.

          Ossai

          1 of 1 people found this helpful
          • 2. Re: Date Parameter - Show Last Friday
            Michael Gillespie

            I think what he's looking for is "the Friday immediately preceding today" - so AT MOST it would be the Friday 7 days ago (if we're on Friday of this week).

             

            Is that correct, Steve?

            1 of 1 people found this helpful
            • 3. Re: Date Parameter - Show Last Friday
              Bryce Larsen

              Believe the above response should work, alternatively, you could do something like:

               

              IF DATEPART("weekday", {MAX([Date])}) >=6 THEN //already Friday or Saturday

              DATEADD("day", 5, DATETRUNC("week", {MAX([Date])}))

              ELSE

              DATEADD("day", -2, DATETRUNC("week", {MAX([Date])}))

              END

               

              This is under the assumption your week starting is Sunday. If your data is updated frequently you could use TODAY() instead, but I typically avoid this in case any ETL issues. I'd also recommend storing {MAX([Date])} in its own field to use instead.

              1 of 1 people found this helpful
              • 4. Re: Date Parameter - Show Last Friday
                Stephen Groff

                Sorry for the late reply... meetings...

                 

                But yes, Michael, looking for the "Friday immediately proceeding Today"

                 

                Let me try out the suggestions above... will report back shortly.

                 

                Thanks Gents

                1 of 1 people found this helpful
                • 5. Re: Date Parameter - Show Last Friday
                  Stephen Groff

                  I found use for both of the answers provided in this discussion.  Thank you both, worked great.

                  1 of 1 people found this helpful