2 Replies Latest reply on Dec 6, 2018 2:14 PM by josh McFarlane

    Commodity Futures Price Full Year Average

    josh McFarlane

      I am using 2018.2.0 Tableau

       

      I am working with a data set of commodity futures.  The work book I have uploaded has 2018 prices each day from June 1 2017 through November 26 2018.  Each data point represents the price for delivery in a certain 'Contract Month'  as of a specific 'Report Period'.  As I move forward in time, some 'Contract Months' become actualized and drop out of the data.  The last data point in the database for a given 'Contract Month' is the settlement price for that month. 

       

      I am trying to calculate the average price for Jan-Dec 2018 on each 'Report Period'.  Early in the time period, this is just the average of 'Close Price' for each 'Contract Month' on a specific 'Report Period'.  As I move forward in time, I need to calculate an average based partially on settlement prices and partially on future 'Close Prices'

       

      For example, on March 1, 2018, the average should be calculated with Jan & Feb settlement prices of 2.20 & 2.11, and March-December futures prices.

       

       

      Is there a way to do this with a formula?  Or is there a way to make the last data point in this table to fill down to the bottom of the table? 

       

      I had tried formulas with ISNULL - but it did not work since the blank data points are not null, they just dont exist in the database.

       

      Appreciate your help,

       

      Josh

        • 1. Re: Commodity Futures Price Full Year Average
          Michel Caissie

          Josh,

           

          I hope I understood the requirement correctly.

          I didn't find an easy way to do it. I checked first by using table calcs, but the computing would require a bunch of dimensions in the detail of the final view, and it would get complicated, if possible, to get the desired view.

          I finally got the numbers  using  a bunch of lods.

           

          Check on sheet ContractYearAvg  if you have the correct numbers. Note that if you do the math with a calculator using the two decimals values you have on the Monthly sheet, you will have slightly different values , since i didn't round anything in the various calcs.

           

          Step 1 is to get the latest values that we will use for the empty months

          First I get the LastReportPeriodWithData  using

          {FIXED DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]): MAX( [Report Period] )}

           

          Next I get the Close Price conversion avg  for those days with

          {FIXED [Product Name],DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]),DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):

          AVG( if [Report Period] = [LastReportPeriodWithData]  then [Close Price Conversion] end )

          }

           

          Next I make a calculation for each month returning this  last  Close Price Conversion avg  using

          {FIXED [Product Name]:MIN( if DATENAME('month', [Contract Month]) = 'January' then [Close Price Conversion (avg on last day)] end )}

           

          Step 2 is to compute the Contract Year average.

          To do so, I count the number of month with data  for a given Period day,  I sum those  averages , I add the missing month averages, and I divide by twelve.

           

          So the number of month with data would be

          {FIXED [Product Name],DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):COUNTD( DATEPART('month', [Contract Month]))}

           

          I will need the avg  Close Price Conversion per contract month  , per Period day with

          {FIXED [Product Name],DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]),DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):

          AVG([Close Price Conversion])

          }

           

          the sum of those months averages with

          {FIXED [Product Name],DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):SUM([Close Price Conversion (avg per  contractMonth-periodDay)])}

           

          and  finally  the  Contract Year average with

          (

          case [nb contract month with value]

          when 2 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]+

          [LastValueJune]+

          [LastValueJuly]+

          [LastValueAugust]+

          [LastValueSeptember]+

          [LastValueOctober]

          when 3 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]+

          [LastValueJune]+

          [LastValueJuly]+

          [LastValueAugust]+

          [LastValueSeptember]

          when 4 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]+

          [LastValueJune]+

          [LastValueJuly]+

          [LastValueAugust]

          when 5 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]+

          [LastValueJune]+

          [LastValueJuly]

          when 6 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]+

          [LastValueJune]

          when 7 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]+

          [LastValueMay]

          when 8 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]+

          [LastValueApril]

          when 9 then

          [LastValueJanuary] +

          [LastValueFebruary] +

          [LastValueMarch]

          when 10 then

          [LastValueJanuary] +

          [LastValueFebruary]

          when 11 then

          [LastValueJanuary]

          else 0

          end

          +

          [Close Price Conversion (SUM per day of report)]

          )

          /12

           

          Check  the sheets  ValidateData1, ValidateData2  and  ContractYearAvg

           

          Michel

          • 2. Re: Commodity Futures Price Full Year Average
            josh McFarlane

            Thank you Michel,

             

            Your solution worked.  Thank you for the work you put into it, it was not an easy solution so I appreciate it.