Running Total on a calculated column

Question

How can I get a running_total in results2 of the calculated column green in results1. My code below is not producing the desired result. Thanks.

num running_total
-----------------

0   True 
0   True 
0   True 
0   True 
0   True 
0   True 
0   True 
1   False 
1   False 
1   False 
1   False 
1   False 
0   True 
1   False 
1   False 
1   False


 Sub Main

    Dim runT1 As integer

    Dim results1= //some code

    Dim results2=
    From v in results1
    Select New With {.num = v.green, .running_total = (runT1 = runT1 + v.green)}

    results2.dump()

 End Sub

score 2 · Answer 1 · answered Aug 03 '11 at 04:38

There are a number of ways to solve this one using a linq-based approach.

If you're using VB.NET 4.0 then you can use multi-line lambdas which will give you a solution closest to the one that you were trying:

Dim runningTotal = 0
Dim getRunningTotal As Func(Of Integer, Integer) = Function(n)
    runningTotal += n
    Return runningTotal
End Function

Dim results2 =
    From v In results1
    Select New With { .num = v.green, .running_total = getRunningTotal(v.green) }

The gotcha on this approach is that if you evaluate the result2 query more than once you'll compound the running total (ie keep counting from last time). The same would have happened in your code anyway.

In C# I would create an extension method using iterators for this, but at this point in time VB.NET doesn't support iterator functions, so we have to choose some funkier ways of doing it.

The first is using the Aggregate & Zip extension methods:

Dim results2 =
    results1.Zip(
        results1.Aggregate(
            New Integer() { },
            Function(tt, t) _
                tt.Concat(New Integer() { tt.LastOrDefault() + t.green }).ToArray()
        ),
            Function (r1, rt) New With { .num = r1.green, .running_total = rt }
    )

Yes, it does work, but it isn't overly efficient as it needs to build successive intermediate arrays for each element in the source collection.

The final approach is to use the Scan extension method available from the System.Interactive library produced by the Rx team at Microsoft. Scan works like Aggregate but produces the intermediate values automatically and hence is much more efficient.

Dim results2 =
    results1.Scan(
        New With { .num = 0, .running_total = 0 },
        Function (tt, t) _
            New With { .num = t.green, .running_total = tt.running_total + t.green }
    )

Or:

Dim results2 =
    results1.Zip(
        results1.Scan(0, Function(tt, t) tt + t.green),
        Function (r1, rt) New With { .num = r1.green, .running_total = rt }
    )

Simple, huh?

score 0 · Answer 2 · answered Aug 03 '11 at 01:51

It's been a while since this question was asked, and you may well have solved this by now, but, just in case...

I suspect your issue is actually that VB uses "=" both for boolean comparison, and for assignment.

Where you have:

.running_total = (runT1 = runT1 + v.green)

VB is treating it the same as C# code that reads:

.running_total = (runT1 == (runT1 + v.green))

I haven't used VB.net in quite some time, but, if the += operator is supported, try using that instead, i.e.

.running_total = (runT1 += v.green)

Running Total on a calculated column

2 Answers2