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

# Running_Sum on a Previous Calculated field

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.