Variation of Month to Date and Year to Date

[Bennie47250]

Using version 7.0

Have a report with 3 sub reports. The main report and one sub report uses the time period of MonthtoDate to select the records. Another sub report uses YeartoDate. And the last sub uses LastfullWeek.

The date field in the database is always a Sunday. And on Monday of each week we enter data for the quantity produced the prior week. Then sometime between that week on or before on Thursday the report will be ran.

The sub-report that selects records based on the LastfullWeek will always be OK as the last full week is always the last full week.

The sub-reports that use YeartoDate and MonthtoDate will be incorrect as the reports could be ran anywhere between the 1st and 10th of any month to report on the prior month or year data but the report will pull the current month or year data.

How can I properly condition for this issue?

I don’t think I can use something like “if Day (CurrentDate) <7
then {table.DateField} in LastFullMonth
else {table.DateField} in MonthToDate”

As in some months, if the report was ran on the 7th, it should pull the MonthToDate data.

I also have the same issue with the YearToDate.

Appreciate your help in resolving this.

Thanks
Bennie

PS I was unable to search for the archive for similar postings as the search was down when I posted this.

 

[MrBillSC]

I don't think you can use the MonthToDate and YearToDate functions as they always include dates up-to and including today's date. You will probably have to calc or prompt for a cut off date and select your YTD and MTD records based on the cut off date.

MrBill

 

[lbass]

Try something like:

if Day (CurrentDate) <7
then {table.DateField} in dateadd("m",-1,currentdate-day(currentdate)+1) to currentdate - day(currentdate) else {table.DateField} in MonthToDate

Not sure how you want to treat yeartodate. Maybe something like:

if Day (CurrentDate) <7
then {table.DateField} in dateadd("m",-month(currentdate)+1,
currentdate-day(currentdate)+1) to currentdate - day(currentdate) else
{table.DateField} in YearToDate

-LB

 

[Bennie47250]

Unless someone has a better solution, I believe that MrBill is correct that the user will be required to enter the cut off date.

lbass, the 7 in the "if Day (CurrentDate) <7" calculation was an approximated number. Sometimes if the date is 7 I do want to pull the full year.

I need a way to replace the 7 with a number that will give me what I need or another way to calculate what I need. I may try playing around with the week of the month. Seems like if the current date is the first week of a month, then pull previous month, if greater than first week of the month, then pull month to date. If I can get this to work, will look at how to do this for the year also.

Other suggestions are welcomed.

 

[lbass]

I think there is a way to do this if you are clear on what your rules are about when to pull month to date versus the previous month and yeartodate versus year through the last month. If you can clearly state the rules, we can code to adapt to those rules.

-LB

 

[Bennie47250]

lbass, Thanks for hanging it there with me, I have been thinking about the rule and I believe the following will do it.

Anytime the report is ran, determine the date of the week ago Sunday. Then with this date, determine the date for the first day of that month and the first day of the year for that year.

Some examples of the logic:

Had the report been ran on Monday 11/29/04.
The date for the week ago Sunday is 11/21/04. So the Month to date report would pull records between 11/1/04 and 11/21/04 and the Year to date would pull 1/1/2004 to 11/21/04.

If the report was ran today (12/2/04), the week ago Sunday is determined (11/21/04) and the month to date report would need to pull records dated between 11/1/04 and 11/21/04. The year to date records would pull records dated between 1/1/04 and 11/21/04.

If we fast forward to running the report on Thursday 1/6/05.
The date for the week ago Sunday is 12/26/04. So the Month to date report would pull records between 12/1/04 and 12/26/04 and the Year to date would pull 1/1/2004 to 12/26/04

Fast forward again to Friday 1/14/05.
The date for the week ago Sunday is 1/2/05. So the Month to date report would pull records between 1/1/05 and 1/2/05 and the Year to date would pull 1/1/2005 to 1/2/05.

I don’t see the report being ran on a Sunday but it may be prudent to consider it. I think the same rule will apply though. If a report was ran on Sunday 11/28/04 the Monthly report should pull records between 11/1/04 and 11/14/04 and the Year to date would be between 1/1/04 and 11/14/04.

As mentioned in the original posting, one of the sub reports pulls records based on the last full week. This time period should be OK since the last full week is always the last full week correct?

Thanks

 

[lbass]

For {@monthtodate} use:

{table.date} in (currentdate-dayofweek(currentdate,crMonday)-7)-
day((currentdate-dayofweek(currentdate,crMonday)-7)+1 to
currentdate-dayofweek(currentdate,crMonday)-7

For {@yeartodate} try:

{table.date} in date(year(currentdate-
dayofweek(currentdate,crMonday)-7),01,01) to
currentdate-dayofweek(currentdate,crMonday)-7

-LB

 

[Bennie47250]

I’m on version 7.0 of Crystal and apparently the crMonday is something added in a later version.

I think I have resolved my own problem by using the min of Last Full Week as for this report, the last full week is always correct.

To determine the first of the month I'm using "Date (Year (Minimum (LastFullWeek)),Month (Minimum (LastFullWeek)) ,01 )"

To determine the first day of the year I'm using "Date (Year (Minimum (LastFullWeek)),01 ,01 )"

Thanks for your suggestion.