How to use OVER function in cross-table

[Edoardo A]

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:

https://imgur.com/a/kIEQXRt

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!

[Fabian Duerr]

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.

[Edoardo A]

Thanks but I needed to make the average of aggregated value for Period.

I found a way to do it using:

Sum([Weight] * [score] / Sum([Weight]) OVER (Intersect([Period]))) / UniqueCount([Period]) OVER (Intersect([Period]))