2 Replies Latest reply: Feb 8, 2012 9:03 AM by Amir Vastani RSS

    Five Month Rolling Total

      Hey Everyone,

      I'm looking to create a formula that will give me the actual or forecasted cost of a catalog by month, and needs to be able to handle 3 different depending if it is a past month, the current month, or a future month. This will be placed in a pivot table chart that will show catalog cost by month for a year or years. To start off, I have set MM to be equal to the current month in the load script.



      Let MM=Peek('FSCLYRMM',0,'CURRENTDAY')



      Here's some fields that I have and their definitions (FieldName - Definition). These numbers change and we pull in a 'snapshot' every couple weeks that appends the current data onto the end of existing data. The unique identifier of the 'snapshot' is a field called FileDate which is simply the date of the reload.

      • WrittenOff - Actualized catalog cost for a past month
      • TtlCost - The catalogs total cost, the sum of all months that we amortize the cost of the book
      • PercDone - Forecasted percent of catalogs TotalCost to be written off in a given month
      • TtlPercDone - Cumulativepercentof catalogs TotalCost written off so far
      • TrendMonthName - Ties a Month name to the month number through an inline load
      • TrendFiscalMonth - The month number
      • TrendDate - Fiscal Month beginning date



      Here's an explination of what I want based on month.

      Past Month: WrittenOff

      Current Month: (TtlCost * TtlPercDone) -WrittenOff <---Only get Written off for past 5 months. Catalogs repeat every 10-14 months

      Future Month: TtlCost * PercDone



      Here's what I have right now. It seems to be working for past and future months, but not for the current month.


      if((sum([WrittenOff]))<>0,sum({<[Trend Month Name]=,[TrendDate]=>}[WrittenOff])              // Take Written off if it exists (past months)
             ,if([TrendFiscalMonth]=$(MM) and [TrendFiscalYear]=$(YR), sum([TtlCost]*[TtlPercDone]) - sum({<[TrendMonthName]=,[TrendDate]=>}[WrittenOff])              // Current Month
                           ,sum([TtlCost]*[PercDone])))   // future months




      I hope this is clear. Thanks a bunch for your help and let me know if you have any questions!

        • Five Month Rolling Total

          Bump. I'm hoping to get some input in the next 24 hours. Thanks

          • Five Month Rolling Total
            Amir Vastani

            Why don't you compute the rolling Sums in the script and append it to the fact table with a field as 'RollingSum' AS DataType and 'Fact' AS DataType for all the rest


            when you want to do the rolling sum, it would simply be SUM({<DataType={RollingSum}>}FieldName) in case you save the costs to the same fieldname as the Fact table.


            for performance and optimized speed, remove the set analysis and store the Rolling Sum in a new fieldName.


            So ideally, you create a TempTable, and Concatenate the Fact table to that table 3 times, each time, adding the month by 1 and subtracting it by 1 so the data lines up. Now do a SUM() GROUPBY to shrink the table and CONCATENATE it to the FACT table.