4 Replies Latest reply on Sep 23, 2013 10:42 AM by Jackie Klein

    Formula for prior business day

    Jackie Klein

      Is there a way to modify this formula so that if it is Monday, the Date that will be displayed is the prior business day (Friday), if it's Tuesday, display Monday, etc.?

       

      IF [Parameter 3]='Yesterday' THEN DATEADD('day', -1, TODAY( ) ) ELSE [Order Date] END

        • 1. Re: Formula for prior business day
          Shawn Wallwork

          IF [Parameter 3]='Yesterday' AND DATENAME('weekday', [Order Date] ) = 'Monday'

          THEN DATEADD('day', -3, TODAY( ) )

          ELSEIF [Parameter 3]='Yesterday' THEN DATEADD('day', -1, TODAY( ) )

          ELSE [Order Date]

          END


          WARNING: This is untested and may need adjustment.


          Cheers,


          --Shawn

          • 2. Re: Formula for prior business day
            Jackie Klein

            Thanks Shawn.  Ok so I stole the code I provided.  I'm not sure if the base code I provided really lays the foundation for what I'm trying to do.


            I currently have a Parameter for Day, Month, Quarter, and Year values.  I want to add a "Prior Business Day" value to this parameter.  Then I have a calcuated field that updates the date format depending on the Parameter selection (i.e. when Month THEN DATETRUNC('month',[CUST_DATE]), etc.  I'm trying to add a selection so that when the user selects "Prior Business Day" the dashboard automatically shows data for the prior business day, for month, display data for the selected month, etc. 

            • 3. Re: Formula for prior business day
              Tracy Rodgers

              Hi Jackie,

               

              The following should get you close to what I think you want:

               

              case [Date Part Parameter]

              when 'Year' then datename('year', today())

              when 'Month' then   datename('month', today())

              when 'Quarter' then "Q"+ datename('quarter', today())

              when 'Day' then datename('day', today())

              when 'Prior Business Day' then datename('day', today()-1)

              end

               

              Hope this helps!

               

              -Tracy

              • 4. Re: Re: Formula for prior business day
                Jackie Klein

                Hi Thanks for the comments.  I am attaching a sample workbook to better illustrate what I'm trying to do.  I want to add calculations to the sample workbook to allow for the following functionality:

                 

                If "Prior Business Day" is selected from the Parameter PERIOD, default the Order Date to the Prior Business Day based on the following conditions:

                 

                For State = CA and if today = Monday, then Prior Business Day = today()-1; for all other weekdays Prior Business Day is today()-1

                For Sate = WA and if today = Monday, then Prior Business Day = today()-2, for all other weekdays Prior Business Day is today()-1

                For State = FL and if today = Monday, then Prior Business Day = today()-3, for all other weekdays Prior Business Day is today()-1

                Thanks!
                Jackie