How to use TERR Expression Functions in combination with OVER statements ...

Hello all, 

I am trying to work out how to use a TERR Expression Function in combination with Spotfire OVER functions... and if this is not possible, what the work around looks like!! :)

Imagine you have the following data:

Programme    Project    Spend
Alpha        A        10
Alpha        B        20
Alpha        C        30
Beta         A        15
Beta         D        25
Beta         E        35

For the purposes of this question, I'm going to define my own sum function in R -- in all reality I want to generate my own IRR function using something like this

my.sum <- function(
out <- sum(
output <- my.sum( = input1)

Now, if I add a Calculated Column with something like:


... I will get the total sum of the column, as expected. However, I want to able to use the function against the categorical hierarchical data -- that is, as some point I will want to use my function against the Spend at a Programme level, and at other times use it against the Spend recorded at a Project level. 

As such, I tried to use:

MySum([Spend]) OVER ([Project])

... as a new Calculated Column, and in short, this doesn't work: I get an error message saing that OVER can only be used with aggregation methods. 

Therefore, any thoughts on how I can achieve what I'm after, which is either:

MySum([Spend]) OVER ([Project])

... or ...

MySum([Spend]) OVER ([Programme])

... depending on the use case? 

(2) Answers