How to create cumulative aggregations on external data with post-aggregation expressions.

How to create cumulative aggregations on external data with post-aggregation expressions.

When working with in-database data tables, the only functions allowed in custom expressions in calculated columns and in visualizations are those functions available on the external data source. This means Spotfire specific functions like the OVER functions are not available. It may not be obvious how to use the OVER function to create cumulative aggregations like the following cumulative sum on the y-axis of a visualization:

Sum([myDataColumn]) OVER AllPrevious([Axis.X])

If the same expression is used in a visualization based on an in-database data source, then you may see the following error in the visualization: "Could not find function 'AllPrevious'" (or other function name like 'Previous', "Next", etc). With the THEN keyword, it is possible to perform these cumulative aggregations.

Use the THEN keyword to create a post-aggregation expression. For example, this can be used on the y-axis of a bar chart to show a "Cumulative Sum" over all previous x-axis values:

Sum([Sales Total]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

This "Cumulative Sum" aggregation is available as an aggregation by default in the visualization axis column selector when the column is a numeric data type, but other aggregation functions can be used as well, by manually editing the axis expression. To do this, right click on the axis selector, then choose "Custom expression".

For example:

Count([Record No]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

Note: This requires the x-axis to be categorical, like:

<Month([Date])>

Doc: Using Expressions on Aggregated Data (the THEN Keyword)

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_using_expressions_on_aggregated_data_the_then_keyword.htm

Doc: Working With In-Database Data

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/data/data_working_with_in-database_data.htm