How to group Expression Function results in Calculated Columns or visualization axes.

How to group Expression Function results in Calculated Columns or visualization axes.

When using Expression Functions in Calculated Columns or visualization axes, you may want to aggregate the expression function results within groups, similar to using aggregations with the OVER function in standard Spotfire functions like "Sum([myColumn]) OVER ([myGroupColumn])".

Instead of using an OVER function, you need to perform any grouping within the expression function itself. For example, assuming you have a basic expression function which sums all values in the input column, with the name mySum()

=============================================

# Define custom function using R syntax, which will be run using the Spotfire desktop client's built-in TERR engine. This example is a basic sum:

mySum <- function(input)

{

out <- sum(input)

out

}

# Run the function to produce the output, repeat for length of original column

output <- rep( mySum(input1), length(input1) )

=============================================

Assuming a data table like:

=============================================

myGroup value

Alpha 10

Alpha 20

Alpha 30

Beta 15

Beta 25

Beta 35

=============================================

A calculated column with the expression "mySum([value])" would result in:

=============================================

myGroup value Basic Expression Function

Alpha 10 135

Alpha 20 135

Alpha 30 135

Beta 15 135

Beta 25 135

Beta 35 135

=============================================

But if you wanted to perform a sum of those values aggregated by a categorical grouping by values in a second column, similar to an out-of-the-box "Sum([value]) OVER ([myGroup])", you would add a second parameter and use that to do the grouping within your expression function:

=============================================

mySum <- function( my.in, my.group )

{

#align the values of my.in with group values of my.group

df <- data.frame( cbind( my.in, my.group) )

#create id column to preserve original order

df$id <- 1:nrow(df)

#perform calculation by groups. e.g., aggregate() with FUN=sum

myagg <- aggregate( my.in, by = list(my.group), FUN = sum, na.rm = TRUE )

#merge the combined data frame with the results, aka "left join" to align aggregates with individual rows

out <- merge( df, myagg, by.x = "my.group", by.y = "Group.1" )

#output just the aggregate results column, in original order

out[ order(out$id), ]$x

}

output <- mySum( my.in = input1, my.group = input2 )

=============================================

Now a calculated column with the expression "mySum( [value], [myGroup] )" will result in:

=============================================

myGroup value Expression Function - Calculation with Grouping

Alpha 10 60

Alpha 20 60

Alpha 30 60

Beta 15 75

Beta 25 75

Beta 35 75

=============================================