2 Replies Latest reply on Jul 6, 2018 11:44 AM by Steve Rohlf

    How to set max date to always be the same?

    Steve Rohlf
    Steve Rohlf Jul 6, 2018 9:59 AM

      We have a business week of Monday through Sunday.  Data is updated weekly on Sunday nights/Monday mornings (usually around 3am). I am grabbing the last 4 weeks of results.  The code {FIXED : MAX(DATE([Settled Date]))} works good when we have data on Sunday but about once a month there is no activity on Sunday.  When this happens it causes the max day to be Saturday.  This causes the last 4 weeks to grab back 1 day to far.

       

      This is what I use as the end date(named Max Day):  {FIXED : MAX(DATE([Settled Date]))}

      This is what I use as the start date (named Min Day): {FIXED : MAX(DATE([Settled Date]))-27} this would work the same: DATE([Max Day]-27) since they use the same end date. 

       

      I am trying to figure a way to set the end/max date to always be the Sunday of the week.  I can get around this if I change the end date to:  {FIXED : MAX(DATE([Settled Date]))+1}

      However this means next week when there probably is data from Sunday it will now be the wrong end date.  I can manually do this on Monday mornings but it would be better if I didn't have to do anything, which this would work if there always was Sunday data.

       

      At least my labeling showing the dates being used works correctly if it is Sunday or Saturday for the last date using this: 

       

      (STR(MONTH([Min Day])) + "/" + STR(DAY([Min Day]))+ "/" + STR(YEAR([Min Day]))

      + " - " +

      STR(MONTH([Max Day])) + "/" + STR(DAY([Max Day]))+ "/" + STR(YEAR([Max Day])))

       

      The end results use the following:

       

      IF [Settled Date] >= [Min Day]

      AND [Settled Date] <= [Max Day]

      THEN [Revenue] END

       

      An example of this is the last 4 weeks.  July 1st was Sunday so it would set Max Day as 7/1/2018 and Min Day as 6/4/2018 but with no Sunday data I am ending up with Max Day as 6/30/2018 and Min Day 6/3/2018.

      • 76 Views
      • Categories: Dates & Times
      • Tags:
        • 2. Re: How to set max date to always be the same?
          Steve Rohlf
          Steve Rohlf Jul 6, 2018 11:44 AM (in response to Ken Flerlage)

          That works using what one person posted there, not sure it was the one people marked as the right answer but it was the one that worked for me. 

           

          Made a new calculation called Max Date Calculation: (I really don't need past Saturday or Friday as last date but figured doesn't hurt to have the whole week covered.

           

          IF DATENAME('weekday',MAX([Settled Date])) = 'Sunday' THEN MAX([Settled Date])

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Saturday' THEN DATE(MAX([Settled Date]) + 1)

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Friday' THEN DATE(MAX([Settled Date]) + 2)

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Thursday' THEN DATE(MAX([Settled Date]) + 3)

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Wednesday' THEN DATE(MAX([Settled Date]) + 4)

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Tuesday' THEN DATE(MAX([Settled Date]) + 5)

          ELSE IF DATENAME('weekday',MAX([Settled Date])) = 'Monday' THEN DATE(MAX([Settled Date]) + 6)

          END END END END END END END

           

          I then changed the Max Day to:

           

          {FIXED : (DATE([Max Date Calculation]))}

           

          This changed the max day for the last 4 weeks from 6/30/2018 to the correct 7/1/2018.  I guess I won't know for sure until I can compare a Sunday and a Saturday as the last day with data.  Hopefully that means this Monday I will know for sure.