Using FirstSortedValue() as Dimension of a Chart

[Rich-HHE]

Hello, I need to plot response rate (orders per piece mailed) as a function of "Customer Age" at the time of the mailing. "Customer Age" is defined in this case as how many months since the last order at the time of the mailing.

When I add the following expression to a pivot chart as an expression, I get the proper values. When I add this expression as a dimension of a chart, it says // Error in calculated dimension.

Here is the expression I would like as a dimension of the chart:

=FLOOR((BULK_MAIL_DROP_DATE-FirstSortedValue(TRAN_PROCDATE, -IF(TRAN_PROCDATE<BULK_MAIL_DROP_DATE and TRAN_AMOUNT>0 and ITEM_PROD_GRP_MAJ='ORD',TRAN_PROCDATE)))/30)

I think there is a logical disconnect that my mind cannot see. Does anyone know what it is and/or how I would accomplish this task?

Thanks in advance,

Rich

[Rich-HHE]

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

[CUSTOMER, MAIL_DATE,

101, 05/15/2012

102, 02/13/2012

102, 11/22/2012

];

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?

Thanks,
Rich

[Rich-HHE]

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)

Thanks,

Rich

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.