Calculating a Running Total Part 2

To finish up the topic that I started in my previous post, I wanted to post a more complex example of running total calculations. This is based off a request on the MSDN forums, for a running total calculation that would “slide” along as additional rows were processed. In the poster’s example, the running total needed to reflect the total for the last 10 weeks.

To show how to accomplish this, I’m going to update the same package that was used in the previous example.

The script component has the same RunningTotal column defined on the output:

The primary difference is in the script itself. Since I want to keep a list of the previous 10 records, I am using a Queue object, which is part of the System.Collections.Generic namespace. This object lets you easily remove the oldest item from the queue, so it works very well for this example. In the script, if the queue has more than 10 items, the oldest item is removed prior to adding a new one. The running total is produced by iterating through all the items in the collection, and storing the result in the RunningTotal output column.

Public Class ScriptMain

    Inherits UserComponent

    Dim runningTotalCollection As Queue(Of Integer) = New Queue(Of Integer)

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If runningTotalCollection.Count >= 10 Then

            runningTotalCollection.Dequeue()

        End If

        runningTotalCollection.Enqueue(Row.OrderQty)

        Dim x As Integer

        Dim sumX As Integer

        For Each x In runningTotalCollection

            sumX += x

        Next

        Row.RunningTotal = sumX

    End Sub

End Class

This is another example of the flexibility of the Script component. It also helps highlight that by using the Script component, you can store the values from previous rows, and then reference them later. The example package is posted on my Skydrive.