Number of Days between orders and Days since last order

jambrose
jambrose used Ask the Experts™
on
I am trying to figure out the Average number of days between orders. (as one field)
My subsummary is displayed as:
Company Name
   Invoice Date  |  Description  |  Amount
   Invoice Date  |  Description  | Amount
   and so on....

And my next field I'd like to include is number of days since last order?

Sorry if this is simple, I'm kind of new at FM Scripting


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
when entering a new record, you can sort existing records, get the date of the last one, create a new one then work out the date difference. this of course will be wrong if you then delete an existing record after creating the next one, but is it a problem. If you want to avoid to sort, etc... at each record creation, store the last record creation date in a global variable and use this to create any new record. don't forget to initialize it at startup.
or, dynamic solution: you need to relate each record to the previous one, then calculate date(current record) -  date (previous record). in this case, you store the Id of the previous record, and use this to relate the table to itself. there again, it goes wrong if you delete (record - 1), so better stick to the 1st solution which
is simpler.

Author

Commented:
I'm looking for a way to calculate this in a sub summary field, to show a customer's average days to order.

The info is imported into FMP as indicated

Company (sub summary) | Field (Avg Number of Days between Orders)
-------------------------------------------------------------------------------
Date / Description / amount
Date / Description / amount
Date / Descirption / amount

Would I make the Avg Number of days field a calculation? or summary?

Commented:
a summary if you read from within the current table, a calc. if read from a parent table for instance.
a summary will break its values on sub-summaries per customer for instance, if dorted accordingly.
the calc. can also be done from within the customer table. so it mainly depends on which table is your layout linked to.
but before you can average the delays, you need to make up this delay from imported data: was that clear?
if from a set of records which is imported, you can make a loop to calc all of them at once. hope you don't have 500000 lines, because this is rather slow to perform

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial