Crystal Reports 2011 Last Tuesday of the Month with rolling history

[PatchesPete]

I have a table that contains 15 months worth of data and is added to every Tuesday, making the AsOfDate always a Tuesday. I do a rolling 12 month history and use the formula below to select the months I need:

{MyTable.AsOfDate} in [dateserial(year(currentdate),month(currentdate)-12,1) to dateserial(year(currentdate),month(currentdate),1)]

It has now been requested that I pull only records from the last Tuesday of each month (rather than every Tuesday in the month) so the above formula no longer works. I need to check the AsOfDate to see if it is the last Tuesday of the month and then pull only the last 12 months of last Tuesdays of the month.

Any suggestions on how this can be done? Thanks for any assistance you can give.

 

[pmax9999]

I can't think of a way to do it at the record selection level. It could probably be achieved using SQL code (eg via command) but I suspect any native CR solution would be crude and inefficient.

One approach would be to create an extra table in the database (could also be done with excel 'table') with just the dates for all last Tuesdays of the month.

An alternative would be to return all records and use conditional suppression of the details section to show only the last Tuesdays of the month - not efficient I know, but easily implemented.

This could be achieved with the following conditional suppression formula:

Month({MyTable.AsOfDate}) = Month(Next({MyTable.AsOfDate}))

Hope this helps.

Cheers
Pete

 

[PatchesPete]

I think I found a way to accomplish what I need.

I created a formula called TestDate:
Local DateVar DIn:= date ({MyTable.AsOfDate}); //date field
Local NumberVar DOW:= 3; //target DayOfWeek Tuesday=3
DIn := (DIn - day(DIn) + 32); // Find a date in the next month
DIn := DIn - day(DIn); // Go to the last day of the original month
if (DayOfWeek(DIn) < DOW )
then DIn := DIn - 7;
DIn - DayOfWeek(DIn) + DOW

Then I put the following code in Record Selection:

{MyTable.AsOfDate} in [dateserial(year(currentdate),month(currentdate)-12,1) to dateserial(year(currentdate),month(currentdate),1)]
and {MyTable.AsOfDate} = {@TestDate}

If I look at the detail of my report, it's pulling the last Tuesday of the month (8/26/2014, 7/29/2014, 6/24/2014, 5/27/2014, etc.) I'm not quite sure why it works but it does.