Using Set Analysis to "Lookup" Corresponding Value

[#1]

I have a fact table with the following (simplified) structure:

DealID, RecordType, Date, Price, Volume

It has (again, simplified) 5 rows:

DealID	RecordType	Date	Price	Volume
1	Hedge Deal	1-1-2011	25	500
2	Hedge Deal	1-1-2011	30	200
3	Hedge Deal	1-1-2011	35	300
4	Hedge Deal	1-1-2011	50	100
5	Market Price	1-1-2011	40	NULL

I would like to create a pie chart that measures opportunity cost by month. That is:

1) Take the hedge price minus the daily market price * the hedge volume for each hedge deal and call that opportunity cost.

2) Sum this by month.

3) Have month as a dimension and this sum as the expression.

4) Pie chart.

The problem is I can't find a way to "look up" the daily market price for each hedge deal. I've tried using the different functions for this (set analysis, aggr(), etc.) but I keep running into the same two problems:

1) I have to include Date and DealID in the dimensions to properly do the opportunity cost math, and then I can't do an aggregrated pie chart.

2) If I just use Month(Date) as the dimension, I can't figure out how to do the opportunity cost math on a per-deal basis - if I use average price, it aggregates to the month and you lose the individual variance.

(If you want to check your math, the opportunity cost for January here is -10,000.)

Anyway, I keep getting tantalizingly close to the solution, so I'm pretty sure it's possible, so if anyone can push me in the right direction, I would appreciate it.

Thanks,

Kyle Hale

[johnw]

I see no advantage to set analysis over a script solution, even if we could come up with an expression that worked.  The opportunity cost calculation can be done per ID, so it isn't a script aggregation (which we'd want to avoid).  We're just saving the user objects time, and making our expressions simpler.

See attached.  Script below.

Data:
LOAD * INLINE [
DealID RecordType Date Price Volume
1 'Hedge Deal' 1-1-2011 25 500
2 'Hedge Deal' 1-1-2011 30 200
3 'Hedge Deal' 1-1-2011 35 300
4 'Hedge Deal' 1-1-2011 50 100
5 'Market Price' 1-1-2011 40
] (delimiter is ' ');

LEFT JOIN (Data)
LOAD
'Hedge Deal' as RecordType
,Date
,Price as MarketPrice
RESIDENT Data
WHERE RecordType = 'Market Price'
;
LEFT JOIN (Data)
LOAD
DealID
, (Price - MarketPrice) * Volume as OpportunityCost
RESIDENT Data
WHERE RecordType = 'Hedge Deal'
;
DROP FIELD MarketPrice
;

[Calendar]:
LOAD *
,date(monthstart(Date),'MMM YYYY') as Month
;
LOAD date(fieldvalue('Date',recno())) as Date
AUTOGENERATE fieldvaluecount('Date')
;

[#3]

I have considered going back to the script to introducing the opportunity cost as a new measure (This is how we do it in the OLAP cube that sits on top of the fact table.)

But ultimately what I was hoping was that we wouldn't have to go to the scripts to be able to generate this sort of expression, so that our users could come up with these sorts of calculations (of which there are many more above and beyond this one example) and we could supply them with the syntax to achieve it without a script load.

[johnw]

One of the fundamental limitations with set analysis is that a set is built at the level of the entire chart, not at the level of a row on the chart.  So a set expression designed to do this:

(Price - only({lookup the market price record for the date} Price)) * Volume

Would stop working the moment there was more than one date.  And of course if there was only one date, there's be no need for a lookup.  In other words, you can't use set analysis to do a lookup, or at least I personally don't think it's possible.

Now, I'm not entirely sure why the firstsortedvalue() portion of the below expression works, but it seems to work, and the whole expression seems to work.  There's probably a simpler expression, but this is what I have.  This of course isn't something I'd ask a user to type in.

sum(aggr(
Volume * (only({<RecordType={'Hedge Deal'}>} Price)
- aggr(nodistinct firstsortedvalue({<RecordType={'Market Price'}>} Price,Date),Month,Date))
,Month,Date,DealID))

See attached.