1 Reply Latest reply on Dec 15, 2017 1:12 PM by Patrick A Van Der Hyde

    Finding the average value from a monthly running sum

    David Irvine

      Problem Summary.

      I am trying to calculate the 'Average monthly out flow' defined as average percentage of total population at beginning of month that left in next month.

      In a transaction form. Time Stamp | Previous Stage | Next Stage |.

      I am Calculating Population Size as a Running sum of [Next stage = Target Stage] - [Previous Stage = Target Stage]

       

      Restrictions.

      I am trying to use a built in join to an external MA platform. So my ability to reshape data is limited. Also means i can't share a same workbook easily, Hopefully the above description gives enough context.

      Data set will also be quite large so running time will also be a factor to consider.

       

      Current State:

       

      The Last line is a test that doesn't work.

      Output:

      The End Goal is the Average for the 3rd line which would be just under 0.5%. I am aware that i could do this by calculating using the current number of records in the stage and whilst currently to total number stays consistent it is likely to change more significantly and that needs to be reflected in the calculation.

      I suspect there is a simple trick that i could use or its not possible.