-
1. Re: How to set max date to always be the same?
Ken Flerlage Jul 6, 2018 10:03 AM (in response to Steve Rohlf)1 of 1 people found this helpfulTake a look at this discussion: Calculated field for last day of a given week (not the weekly average)
-
2. Re: How to set max date to always be the same?
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.