Hi,
I want to calculate the average days between the moment a customer places an order, and the moment the order is delivered, weighted by the number of items ordered. For example 2 pieces in order 1 with order date of 10-8-09 and delivery 15-8-09 and 4 pieces in order 2 with orderdate of 8-8-09 and delivery date 15-08 would give 2x5 days+4x7 days=38 days divided by 6 pieces = 6,66as an average number of days.
With the NL function I am able to create the 3 arrays I need. However, how do I calculate this within just one cell.
Should I use the excel matrix functions by putting the arrays in 3 different cells and then multiply and divide with an excel matrix function?
Patrick Leunissen
Operations Manager
Entertainment Retail Group
The Netherlands
Date
Votes
3 comments
-
Jet Reports Historic Posts Try the following:
C4 = <<Order No. Filter>>
D4 = NL("Sum","Sales Header","=Local(NF(,""Shipment Date"")-NF(,""Order Date""))","Document Type","Order","No.",C4)
E4 = NL("Sum","Sales Line","Quantity","Document Type","Order","Document No.",C4)
F4 = IF(E4=0,0,D4/E4)
C4 is your document number filter, D4 calculates total number of days between Shipment Date and Order Date, E4 calculates total quantity, and F4 makes the division with an if zero check. -
Jet Reports Historic Posts Hello Sebastiaan,
Thanx for your support.
This does not work since the calculation is for every row in the database you calculate (shipment date-order date)xquantity so you cannot calculate the sum of the quantity for all rows seperately. You have to do it within your "=Local(NF(,""Shipment Date"")-NF(,""Order Date""))" part of the formula
Patrick -
Jet Reports Historic Posts Correction:
C4 = <<Order No. Filter>> D4 = NL("Sum","Sales Header","=Local(NF(,""Quantity"")*(NF(,""Shipment Date"")-NF(,""Order Date"")))","Document Type","Order","No.",C4) E4 = NL("Sum","Sales Line","Quantity","Document Type","Order","Document No.",C4) F4 = IF(E4=0,0,D4/E4)
Definition:SUM(Quantity * (Shipment Date - Order Date)) / SUM(Quantity)
Please sign in to leave a comment.