Comparative week in a report

rsfairbanks
rsfairbanks Member Posts: 107
edited 2005-07-07 in NAV/Navision Classic Client
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

Comments

  • Albertvh
    Albertvh Member Posts: 516
    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
    rsfairbanks Member Posts: 107
    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
    Albertvh Member Posts: 516
    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 :D
    Albert[/code]
  • rsfairbanks
    rsfairbanks Member Posts: 107
    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

Categories