Jump to content

How to use OVER function in cross-table

Edoardo A

Recommended Posts

Hi to everyone,

I'm trying to calculate a value in a cross-table where aggregation is done also on a column that's not present in the Cross Table.

I try to explain better: if I use expression "Sum([Weight] * [score]) / Sum([Weight])" in the following cross-table:


I obtain 84,38. Instead, I need to aggregate values on "Period" first and then make the average of those values. So the desidered value should be (84,82 = 83,5+86,14/2).

As you can see in the attached dxp, I found a way to do it using multiple calculated columns but I need to calculate it it directly in the cross-table.

I tried to use this expression in the cross-table:

Average(Sum([Weight] * [score]) / Sum([Weight]) OVER ([Period]))but I got an error.

Can anyone help me

Thanks in advance!

Link to comment
Share on other sites

Your first period has 8 rows, your second period has only 4 row.


Therefore, the average calculated in the Spotfire cross table will be (83,5 * 8 + 86,14 * 4) / 12 = 84,38


This is the correct calculation for the underlying data.


You could add one more column in your data set to take into account this difference in data rows.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...