Date array for report

cwiginton · 2009-06-30

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

garak · 2009-06-30

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

Savatage · 2009-06-30

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

Date array for report

Comments

Categories