0 Replies Latest reply on Oct 2, 2013 8:31 PM by Angela Gaddy

    Running_Sum on a Previous Calculated field

    Angela Gaddy

      I need support on a running sum calculation that is partition on month (MY) and a product number field. I am trying to take a running sum calculation and minus the current month for each occurring month..  Each time I place the calculation on the Measure pane, the calculation will change all of my other RUNNING_SUM calculations, EXCEPT the measure that is pulled in directly from the MEASURE shelf. I have four other calculations that are also dervived from a basic SUMIF/SUMPRODUCT excel function.  I am able to get all other partitioned calculatios to function correctly.  Any thoughts, I spent a lot of time looking at many of the post from the past three (3) years; which were great, now I need some help..

       

      Calculations:

      1. Cost

          Cost from the measure shelf

       

      1a.Find the percent of each row cost and multiply by the total cost

          SUM([01.Costs ])/TOTAL(SUM([01.Costs ])) --- Totals summarize values from the Table (Across)

       

      1b. Cost % (1a) minus a couple dimensions

      2a.  Sales multiplied by Cost% (1b)

           (MAX([Sales])*[01b.Cost%)])

       

      2b. MAX([Sales])*[01b.Cost% )]) ---- each sale is rolled up to only 1 product number, which is why I find the % of each cost so I can multiply by the MAX sales to get the allocated sales.

       

      3a. YTD - Running Sum Cost

           RUNNING_SUM(SUM([01. Costs ]))

       

      3b YTD - Running Sum Sales

          RUNNING_SUM(LOOKUP([02b.Allocated Sales],0))

       

      4a. ERROR occurs

      //This is a running_sum or previous_value calculation to return the YTD minus the current YTD.

      [03b.Alloc Sales] - LOOKUP([03b. Alloc Sales],-1)

       

      4b. Sames as 4a. used to allow 4a. to occur and then minus the current YTD.

           [04a.CP - Sales]-[03b.LTD Alloc Sales]

       

      5. Cost Margin

         [02b.Allocated Sales]-(SUM([01. Costs ]))

       

      6. Cost Margin %

         [05.CM ]/[02b.Allocated Sales]

       

       

      I have attached two screen captures -- one with Calculation 1 - 3, 5 and 6 working.  And the other screen capture show what happen when I place calcualtion 4 on the view.  I am partioning and addressing the same (Customer Name, Month/Year and Product Number as my partion and addressing the other dimension fileds .  I left Sort to automatic, At level = deepest and restarting every = None.

       

      I hope someone can assist me.