Calculate Order Frequency

  • 1
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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

Photo of rocketc


  • 824 Points 500 badge 2x thumb

Posted 3 years ago

  • 1
  • 1
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
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.