3 Replies Latest reply: Nov 18, 2011 7:12 PM by John Witherspoon RSS

    Using Set Analysis to "Lookup" Corresponding Value

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

       

      DealID, RecordType, Date, Price, Volume

       

      It has (again, simplified) 5 rows:

       

       

      DealIDRecordTypeDatePriceVolume
      1Hedge Deal1-1-201125500
      2Hedge Deal1-1-201130200
      3Hedge Deal1-1-201135300
      4Hedge Deal1-1-201150100
      5Market Price1-1-201140NULL

       

       

      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

        • Re: Using Set Analysis to "Lookup" Corresponding Value
          John Witherspoon

          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')
          ;

            • Using Set Analysis to "Lookup" Corresponding Value

              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.

                • Re: Using Set Analysis to "Lookup" Corresponding Value
                  John Witherspoon

                  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.