Spotfire OVER functions are not available directly for external/in-database data since when a data connection is configured to keep the data external, only functions supported by the external data source are present. So in order to do simple cumulative aggregations using OVER functions, the post-aggregation technique needs to be used as shown in KB:

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

But this approach would fail in cases when division or any operation needs to be performed on the y-Axis value without considering other columns used in color axis of the same visualization.

For example, when the requirement is to divide expenses by age group with the total population based on Gender i.e. Expenses/Total Distribution:

Expenses per Gender looks like below:



Distribution per Gender is as below:


Now, trying to achieve the requirement using OVER statement like below:

 Sum([RECEIPT_AVERAGE]) THEN [Value] / Count() Over (Intersect([Axis.X],All([Axis.Color])))

Note that Age Group is a categorical column in color axis so [Axis.Color] is an available option in Over function

You would observe this does not give the expected results as expression Sum([RECEIPT_AVERAGE]) THEN Count() Over (Intersect([Axis.X],All([Axis.Color]))) is just counting each Sum([RECEIPT_AVERAGE]) i.e. 7 age group in each Gender so resulting Sum([RECEIPT_AVERAGE]) would be divided by 7 for each age group.


But that is not result we are looking for as we expect to divide the expenses by total gender count as shown below:

 

This article provides an overview on applying some tricks when using the OVER function while having to work with external data.

The requirement described above cannot be achieved using the OVER Statement Post-aggregation technique. Instead, you can use the following workarounds:

Option 1: Using duplicate external data table.

1. Add the same data table again as external,for example "STORE2". STORE is already added in as external table. See Accessing Data from External Sources for more information.

2. Remove all column matches between the two tables except on GENDER column, or at least remove the column match on AGE GROUP column (which is used in color axis of bar chart).

3. In above bar chart, use Sum([RECEIPT_AVERAGE])/count([STORE2].[GENDER]) expression in Y-Axis which will yield the following result. See Working with Multiple Data Tables in one Visualization and Column Matching for more information.



Now, the bar chart shows the expected result. There would be one warning icon visualization due to column match not being done on the AGE GROUP Column. You can hide the the Visualization Title and instead show visualization title in the Description field as a workaround to avoid showing warning icon.

4. If you intend to use filters, create the data table relation between STORE and STORE2 on a primary or unique key. See Data Table Properties - Relations for more information

5. Then create at least one filter from both tables so they will appear in the Filter Panel. Go to filter panel, besides STORE2 click on "Filtering in related data tables" icon, select "Include Filtered Rows only" from STORE. See Filtering in related data tables for more information.

This way STORE2 data table would also respond to filters of STORE table and update the count([GENDER]) accordingly to show filtered results in bar chart.


Option 2: Using predefined column in Database

1. Create "Total Count" column in the source database which counts the rows for each Gender

2. Then use the newly created column in the bar chart expression like: Sum([RECEIPT_AVERAGE])/First([Total Count])

Note: This predefined column created in database would not respond to filter changes done in Spotfire.

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

• https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-48050

Wiki: TIBCO Spotfire Primer - Blog 2 - Hybrid in-database/in-memory aggregations

• https://community.tibco.com/feed-items/tibco-spotfire-primer-blog-2-hybrid-databasein-memory-aggregations

Doc: Working with in-database data

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

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

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

Doc: Accessing Data from External Sources

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/connect/connect_accessing_data_from_external_data_sources.htm

Doc: Working with Multiple Data Tables in one Visualization

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/md/md_working_with_multiple_data_tables_in_one_visualization.htm

Doc: Column Matching

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/md/md_column_matching.htm

Doc: Data Table Properties - Relations

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

Doc: Filtering in related data tables

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/filter/filter_filtering_in_related_data_tables.htm