Min() Order Date of a Product Group

#1

I want to be able to calculate the first date a company ordered a specific product. I want to then calculate other things from that first order date.

Example. Company orders IceCream-A on 1/1. Then they order IceCream-A and IceCream-B on 2/20.
How can I know that Company ordered IceCream-A for the first time on 1/1, and IceCream-B on 2/20?

There are multiple companies ordering IceCream-A and IceCream-B and I want to know when each Company first ordered each type of Ice Cream.

Then, be able to run formulas in other columns against the first order date of each company’s first order date of each IceCream.

0 Likes

#2

Well, one possibility would be to use linked records to track orders, roll up each customer’s consolidated orders, and extract date values from the rolled-up orders. Or, to rephrase that in a manner that makes actual sense

  1. In your [Customers] table, create a linked-record field called {Orders} that links to an [Orders] table.
  2. Each record in [Orders] should contain a {Date} field and an {Item} field (obviously :wink: ).
  3. Also create in [Orders] a formula field called something like {EncodedOrder}. It should be a fixed-length field combining, say, a 3-digit item code with a 6-digit date code and a couple of separator characters. Assuming an intermediate {ItemCode} field that handles mapping items to item codes, the formula for {EncodedOrder} might be
IF(
	AND(
		{ItemCode},
		{Date}
		),
	{ItemCode}&
	  '|'&
	  DATETIME_FORMAT(
		{Date},
		'YYMMDD'
		)
	)
  1. Define in [Customers] a rollup field called {OrderHistory}. Use the following as an aggregation formula (that is, an Airtable formula pasted into the field where an aggregation function would normally go):

    '|'&ARRAYJOIN(values,'|')&'|'
    
  2. From {OrderHistory}, you can extract a lot of information. For instance, to get the date of the initial purchase of any item, use this formula:

DATETIME_PARSE(
    MID(
		{Order History},
		FIND(
			'|'&{ItemCode}&'|',
			{OrderHistory}
			)+5,
		6
		),
	'YYMMDD'
	)

To find out how many times the customer ordered a special item, try

(LEN(
	'|'&{OrderHistory}&'|'
	)-LEN(
		SUBSTITUTE(
			'|'&{OrderHistory}&'|',
			'|'&{ItemCode}&'|',
			''
			)
		)
	)/5

Caveats: Assumes Orders are placed in sequence.

1 Like

#3

Thank you, @W_Vann_Hall.

I’m going to best testing this today and I’ll let you know who it turns out!

0 Likes

#4

@W_Vann_Hall

I see what you were going for here, but this is not getting me to the final step I want. I want to be able to see each items and its first order date on a separate line, so I can calculate against the first order date of that item.

0 Likes