# Calculate Order Frequency

• 1
• Question
• Updated 3 years ago

I would like to calculate the order frequency of my customers.

My goal is to have a formula text field called [Order Frequency] (Monthly, QuarterIy or Annually).....Using the customers' FOD (first order date) as the base line date unless the customers FOD is before 365 days in the past....then base line date = 365 days from current date.

If a customer has placed orders in each of the past months (from base line Date) then = Monthly.

If a customer has placed orders in each of the past and or present quarters (from base line Date) then = Quarterly.

etc. Annually

I have two tables: Customers & Orders

I believe this would be simple if I could have multiple OR filters on a summary field.

I appreciate any suggestions.

Thank you

• 824 Points

Posted 3 years ago

• 1

Blake Harrison - DataBlender, Champion

• 100 Points
While it may sound simple, this is actually a very complex task to accomplish.  Most of it would be repeating a process for each time period (Month, Quarter, Annual) so I'll just address Monthly. For each Month that you wish to determine a Customer's order status (Yes, they ordered vs No, they didn't), you would need a record that represents one Client in one Month. For example, if you want to know if Acme placed an order in November, 2015, you would need a record for Acme Nov 2015 so that you would be able to use a Summary field from the Orders table to this table to determine how many orders they placed. To do this, you would probably need a Months table in addition to a Client-Months table. For Quarterly and Annual, you would need something similar, but could have the Client-Quarters as a Parent table to Client-Months and have Client-Annual as a parent to Client-Quarter.