I've tried to get by for now by doing this in the LOAD SCRIPT. Ideally, I need to do it as a dimension of a chart, but for now, creating the value in the LOAD will work. This is my attempt and I cannot get it to work. Any ideas on this? I don't think IntervalMatch will work for me in this case.
// GOAL: TO FIND MOST RECENT PURCHASE OF SKU [BBB] PRIOR TO EACH MAILING
mailing: load CUSTOMER, Date(Date#(MAIL_DATE, 'MM/DD/YYYY'),'$(DateFormat)') AS MAIL_DATE inline
sales: load CUSTOMER, SKU, Date(Date#(ORDER_DATE, 'MM/DD/YYYY'),'$(DateFormat)') AS ORDER_DATE inline
[CUSTOMER, SKU, ORDER_DATE
101, BBB, 01/19/2012
101, AAA, 03/30/2012
102, BBB, 04/15/2012
LEFT JOIN (mailing) LOAD CUSTOMER, FirstSortedValue(ORDER_DATE, -IF(ORDER_DATE<MAIL_DATE and SKU='BBB',TRAN_PROCDATE)) AS LAST_BBB_ORDER;
// RESULTING mailing TABLE SHOULD LOOK LIKE THIS:
// CUSTOMER MAIL_DATE LAST_BBB_ORDER
// 101 05/15/2012 01/19/2012
// 102 02/13/2012 -
// 102 11/22/2012 04/15/2012
How do I obtain LAST_BBB_ORDER?
I figured it out in the chart dimension. I needed to AGGREGATE on the Customer ID. Please note that all the calculations that are to be made on a given customer are done inside the Aggr() function and the Aggr() function's second parameter (what to aggregate on) is CUSTOMER_ID. Also note, I switched to MAX() instead of FirstSortedValue() because it was simpler for what I was doing.
=FLOOR((Aggr(BULK_MAIL_DROP_DATE-MAX(IF(TRAN_PROCDATE<BULK_MAIL_DROP_DATE and TRAN_AMOUNT>0 and ITEM_PROD_GRP_MAJ='ORD',TRAN_PROCDATE)),CUSTOMER_ID))/30)
IMPORTANT NOTE: This only works when I select one mailing. When I select multiple mailings (especially multiple mailings that mail to the same customers, I get funky results. I'm still trying to figure out how to aggregate appropriately when a customer is in more than one mailing.