3 Replies Latest reply on Apr 11, 2012 2:01 AM by Jonathan Drummey

    How do I create a conditional running sum?

    Michael Mixon

      Hi,

       

      I've been wracking my brain over this for a while, and haven't been able to figure it out.  I suspect someone here who is smarter than I am can figure it out.  At least I hope so.

       

      I'm attaching a workbook with a sanitized data set, showing what I am trying to do.

       

      Essentially, I want to be able to create a number based on a running sum, but part of the running sum needs to be the result of a condition (i.e. either result A or B for each week).  I'm not sure how to do that without creating a circular reference.

       

      If this is easier to discuss live, drop me a note and I can give you a call.

       

      -Mike

        • 1. Re: How do I create a conditional running sum?
          Jonathan Drummey

          Hi Mike,

           

          On first glance I can see that you have Input C = Metric A - Metric C. That's why you're getting a circular reference, because you state you're trying to calculate Metric A based on Input C.

           

          Other than the calc, there's no other definition of what Input C should be, so I can't help you any more than that.

           

          Jonathan

          • 2. Re: How do I create a conditional running sum?
            Michael Mixon

            Hi Jonathan,

             

            You're right, I forgot to include the definition of Input C, but the calculation is simply Metric A minus Metric C.  And this is the crux of the problem:

             

            Metric A (a starting inventory position) is used to determine what Metric C will be (a constrained customer forecast), which in turn is used to determine what the ending inventory will be (Input C), which in turn is used to determine the following week's starting inventory position (Metric A).  So they're all inter-related, and why I believe a running sum is the way I need to go.

             

            The difficult item is the weekly condition, whereby I need to assess the Metric A number against both the customer forecast (Input D) and the target ending inventory (Target) to determine what Metric C can be.  So, each week's running sum of Metric A needs to be evaluated against that week's Input D and Target to determine Metric C and Input C, which is then fed back into the running sum for the next week.

             

            Does that make more sense?

            • 3. Re: How do I create a conditional running sum?
              Jonathan Drummey

              Hi Mike,

               

              I took another look at this and I'm not able to figure out a logic that works in the time that I have. I made the following diagram of what's going on in the calculations, which shows two circular references, not one, around Metric A, Metric C, and Input C.

               

              conditional running sum.png

              When I run into things like this I usually try to break the circular reference by creating a separate chain of calculations. I'm thinking that you *might* be able to create a "last week's numbers" set of calculations that could be used to feed into the calcs and avoid the circular reference, but I'm not sure. I'm sorry I'm not able to be of more help.

               

              Jonathan