Comparative week in a report

rsfairbanks · 2005-07-05

Hello,

I have a report taking data from the sales Lines for a given accounting week, and I want to also show the data for the same week a year ago.

What is the quickest method?

The user input is Week x or Week -x on posting date on customer date filter, which sets the date range. I then want to have this date filter changed to effectively Weekx|Weekx(a year ago)

Hope this makes sense. Thanks

Albertvh · 2005-07-05

Hi

From what I can gather the user is inputting a week number eg 25 of the current year.
You can use the DWY2DATE function and the syntax is

Date := DWY2DATE(Weekday,Week,Year);
where
Weekday = 1 for Monday etc
Week = 1 to 52
Year = 4 Year digit or if not specified defaults current year

so you could do the following

CurrDate := DWY2DATE(1,InpWeek); // in this case 25
OldDate := CALCDATE('<-1Y>',CurrDate);

Where CurrDate and OldDate are defined as Date variables

Hope this helps.

Albert

rsfairbanks · 2005-07-05

Thanks but not quite.

Users are inputing a date range (i.e Week 21) = 19/06/05..25/06/05.
I then want to get week 21 for the prior year = 20/06/04..26/06/04.

And then set a filter for
Posting date = 19/06/05..25/06/05|20/06/04..26/06/04

Unless a better way is suggested O:)

Albertvh · 2005-07-07

Hi

If the users are entering a date as 19/06/05..25/06/05 then you could do the following

CompStartDate := CALCDATE('<-1Y>',GETRANGEMIN("Date Filter");
CompEndDate := CALCDATE('<-1Y>',GETRANGEMAX("Date Filter");
SETFILTER("Date Filter",'%1|%2..%3',GETFILTER("Date Filter"),CompStartDate,CompEndDate);

This will just subtract a year so you will get 19/06/04..25/06/04

If you must go from a Sunday to Saturday you will have to get the week number from your input date filter.

WeekNo := DATE2DWY(GETRANGEMIN(Date Filter"),2);
Year := DATE2DWY(GETRANGEMIN(Date Filter"),3) - 1;
CompStartDate := CALCDATE('<-1D>',DWY2DATE(1,Weekno,Year));
CompEndDate := CALCDATE('<1W>',CompStartDate);
SETFILTER("Date Filter",'%1|%2..%3',GETFILTER("Date Filter"),CompStartDate,CompEndDate);

This should work

Albert[/code]

rsfairbanks · 2005-07-07

Thank you Alfred, it has put me on track.

It did not quite work for the date we wanted or account for leap years.
Here is my latest code

//Find Accounting year and prior year
AccountingPeriod.INIT;
AccountingPeriod.SETRANGE("Period Type",AccountingPeriod."Period Type"::Year);
AccountingPeriod.SETFILTER("Period Start",'<=%1',GETRANGEMAX("Posting Date")+1);
AccountingPeriod.FIND('+');
Year := AccountingPeriod."Period No.";
AccountingPeriod.SETRANGE("Period No.",Year-1);
AccountingPeriod.FIND('+');
CompStartDate := AccountingPeriod."Period Start";
CompEndDate := AccountingPeriod."Period End";
// Find Week
AccountingPeriod.SETRANGE("Period Type",AccountingPeriod."Period Type"::Week);
AccountingPeriod.SETFILTER("Period Start",'>=%1',GETRANGEMIN("Posting Date"));
AccountingPeriod.SETFILTER("Period End",'<=%1',GETRANGEMAX("Posting Date")+1);
AccountingPeriod.SETRANGE("Period No.");
AccountingPeriod.FIND('-');
WeekNo := AccountingPeriod."Period No.";
IF WeekNo = 53 THEN WeekNo := 52; //Ignore week 53
AccountingPeriod.SETRANGE("Period No.",WeekNo);
AccountingPeriod.SETFILTER("Period Start",'>%1',CompStartDate);
AccountingPeriod.SETFILTER("Period End",'<=%1',CompEndDate);
AccountingPeriod.FIND('-');
CompStartDate := AccountingPeriod."Period Start";
CompEndDate := AccountingPeriod."Period End";

SETFILTER("Posting Date",'%1..%2|%3..%4',GETRANGEMIN("Posting Date"),
GETRANGEMAX("Posting Date"),CompStartDate,CompEndDate);

Obviously I am not a programmer, but it works \:D/

Thanks
Richard

Howdy, Stranger!

Quick Links

Categories

Comparative week in a report

Comments