Pulling Specific Date Ranges 1

[KalebsDad78]

ODBC - CR XI

I am needing to create report that pulls the date ranges where the first Monday of the month is the focus. However, I need the date range to pull the Thursday prior to the first Monday of the month.

Along with that, even if it is a seperate formula, I need to show a comparison from previous years vs the current period.

I searched numerous pages of posts and the FAQ's and couldn't find what I needed.

Cna someone help me with a formula to find this data?

Thanks in advance...

 

[lbass]

This formula will give you the previous Thursday:

datevar firstday := date(year({table.date}),month({table.date}),1);
numbervar dow := dayofweek(firstday);
datevar prevThu;

if dow >= 5 then
prevThu := firstday - dow + 5 else
prevThu := firstday - dow - 2;
prevThu;

This doesn't really address the range issue, but you didn't clarify what you intended to do with the range or what the end of the range would be.

I can't really respond to your other issue. You need to show us what fields you working with and their datatypes, and also define what you mean by the same period last year.

-LB

 

[KalebsDad78]

I thought after reading my post that it might have been misleading.

I do need the Thursday prior to the first Monday of each month but I need it to pull the data from the days between that Thursday-Monday.

Thanks and sorry for not posting a more clear picture of what I needed.

 

[lbass]

Well, it won't be a fast report, but you could use the following in your record selection:

datevar firstday := date(year({table.date}),month({table.date}),1);
numbervar dow := dayofweek(firstday);
datevar prevThu;

if dow >= 5 then
prevThu := firstday - dow + 5 else
prevThu := firstday - dow - 2;
prevThu;
{table.date} in prevThu to firstday

-LB

 

[KalebsDad78]

That worked perfectly lb and the report generated very quickly.

Thanks for your expertise, as usual.