How to create cumulative aggregations on external data with post-aggregation expressions.
Article ID: KB0075434
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All Versions |
Description
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.
Issue/Introduction
How to create cumulative aggregations on external data with post-aggregation expressions.
Resolution
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:
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:
Note: This requires the x-axis to be categorical, like:
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])>
Additional Information
Doc: Using Expressions on Aggregated Data (the THEN Keyword)
Doc: Working With In-Database Data
Feedback
Yes
No
Powered by
