Date Posted:
Product: TIBCO Spotfire®
Product: TIBCO Spotfire®
Problem:
How to group Expression Function results in Calculated Columns or visualization axes.
Solution:
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
Comments
0 comments
Article is closed for comments.