Date array for report

cwigintoncwiginton Member Posts: 16
Hello,

I'm a bit new to report writing in Nav. Current version is 3.7. I need to create a sales analysis report with 20 columns where each column represents sales per week. I do not know the correct date formula to do this.

I'm using the customer table, "Date Filter" field and the Sales$ field.
The report will run automatically so week20 should = CW and week1 should be less 19.

This is how I'd like the report to look:

Cust No. wk1 wk2 wk3 wk4 current week!
##### Sales[1] Sales[2] Sales[3] Sales[4]...........Sales[20]
:-k

Thanks!
Cat

Comments

  • garakgarak Member Posts: 3,263
    20 Coloumns? Oui, it's a wide report :-D
    i := 0;
    DateREc.setrange("Period Type",DateREc."Period Type"::Week);
    DateREc.setrange("Period Start",calcdate('<-CW>-<19W>',Today),calcdate('<CW>',today));
    if DateREc.find('-') then begin
      repeat
        i := i + 1;
        setrange("Date Filter",DateREc."Period Start",DateREc."Period End");
        calcfields("Sales (LCY)");
        WeekArray[i] := format(DateREc."Period Start") + '..' + format(DateREc."Period End"); //this is only to display the filter of the Week
        SalesArray[i] := "Sales (LCY)";
      until DateRec.next = 0;
    end;
    

    not tested, but it should work.

    DateRec is a recordvariable of Subtype Date.

    regards
    Do you make it right, it works too!
  • SavatageSavatage Member Posts: 7,142
    There is a post that does sales by customer by month
    viewtopic.php?f=5&t=10082

    It Looks Like This:
    http://savatage99.googlepages.com/Custo ... yMonth.pdf

    It could easily be changed to do weeks instead of months - but 20 columns is going to have been real small print.

    Here's a copy of the .fob
    http://savatage99.googlepages.com/Repor ... yMonth.fob

    Note: once done it's also easily changed to show purchases by vendor by month
Sign In or Register to comment.