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

book

Article ID: KB0080667

calendar_today

Updated On:

Products	Versions
Spotfire Analyst	7.5 and higher

Description

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])".

Issue/Introduction

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

Resolution

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