Example Script: Rolling 200

Below is an example of a rolling 200 calculation that takes data that has already been loaded and creates a new metric that is the rolling 200 average unit price for each product. In this script, the Order Details grain contains records for each order line item. By iterating through each order sorted by date and by product, you can generate a complex calculation such as this.

Select Statement:

Select [Grain(Order Details).ProductID], [Grain(Order Details).OrderID], [Grain(Order Details).Avg: UnitPrice] From [Grain(Order Details)]

Inner Join

Select [Level(Order_Details.Orders).OrderDate], [Level(Order_Details.Orders).OrderID], [Level(Order_Details.Orders).OrderDate] From [Level(Order_Details.Orders)] On

[Grain(Order Details).OrderID]=[Level(Order_Details.Orders).OrderID]

Order By [Grain(Order Details).ProductID],[Level(Order_Details.Orders).OrderDate]

Script:

Dim [Values] As List(Float)

Dim [LastProd] As Integer = -1

Dim [Sum] As Float

Dim [Count] As Integer

If [LastProd] != [Grain(Order Details).ProductID] Then

RemoveAll([Values])

End If

[LastProd] = [Grain(Order Details).ProductID]

[Sum]=0

[Count]=0

Add([Values],[Grain(Order Details).Avg: UnitPrice])

If Length([Values]) > 200 Then

RemoveAt([Values],0)

End If

For [Index] As Integer = 0 to Length([Values])-1

[Sum] = [Sum]+[Values]([Index])

[Count] = [Count]+1

Next [Index]

If [Count] > 0 Then

[L200 Average Price] = [Sum]/[Count]

Else

[L200 Average Price] = 0

End If

[Date]=[Level(Order_Details.Orders).OrderDate]

[ProductID]=[Grain(Order Details).ProductID]

WriteRecord

In this example, you can see examples of variables, conditional logic and looping structures to produce a complex calculation that involves calculating across many records.