Sum Sales Shipment Line by next working day

Hi Guys (and Gals),

I've been tasked with a complicated formula.
The client wants:
Sum Quantity from Sales Shipment Line
Where "Shipment Date" is within the next working date of the "Order Date"

Order Date is a custom field that copies across from the original Sales Order

=nl("Sum","Sales Shipment Line","Quantity","Type","<>''","Item Category Code",$C11,"Sell-To Customer No.",$C$5,"Order Date",$C$6,"Shipment Date",np("DateFilter",nf(,"Order Date"),nl("First","Date","Period Start","Period Start",np("DateFilter",nf(,"Order Date")+1,nf(,"Order Date")+3),"Period Type","Date","Period No.","1..5")))
The idea behind the formula works however the NF(,"Order Date") within the second Date FIlter doesn't actually receive the date.
Hence the formula errors

What it should look like
=nl("Sum","Sales Shipment Line","Quantity","Type","<>''","Item Category Code",$C11,"Sell-To Customer No.",$C$5,"Order Date",$C$6,"Shipment Date","30/03/2012..02/04/2012")So if the Order Date is for today (the 30th) the date filter returns the next weekday in a 3 day filter.

I hope i've been clear but feel free to ask questions.

Cheers
Bromy

Jet Reports Historic Posts March 30, 2012 22:54

Hi Bromy,

The NF with the blank record key (1st argument) is designed to get the value off the current record of the outer function. However, this only works in a calculated filter. The way Excel works is that your nested filter as it is now will get evaluated by Excel BEFORE the NL(Sum) itself gets evaluated so there is no record to get the value for Order date from. A calculated filter is just a string so Excel doesn't evaluate it before-hand. Jet itself re-evaluates the calculated filter for each record in the Sales Shipment Line table that it is summing and get the order date from each record to do the filtering. Changing it to a calculated filter would look more like this:

=nl("Sum","Sales Shipment Line","Quantity","Type","<>''","Item Category Code",$C11,"Sell-To Customer No.",$C$5,"Order Date",$C$6,"Shipment Date","=np(""DateFilter"",nf(,""Order Date""),nl(""First"",""Date"",""Period Start"",""Period Start"",np(""DateFilter"",nf(,""Order Date"")+1,nf(,""Order Date"")+3),""Period Type"",""Date"",""Period No."",""1..5""))")
Does that work for you?

Regards,
Hughes

Sum Sales Shipment Line by next working day

1 comment