Tibco Spotfire OVER function in an IF statement not working as expected

[Stephen Mackenzie 2]

I am a bit confused as to how an OVER statement inside an IF statement is working.

I wrote the question here, so dont want to repeat it (including screenshots).

https://stackoverflow.com/questions/59412557/tibco-spotfire-over-function-in-an-if-statement-not-working-as-expected

 

Fundamentally, it is this formula:

If([Region]="North",Count() OVER ([Product]),Null)The Count() Over seems to have had its partition adjusted by the outer IF statement, where I expected the formula to only give a output on the North region rows, but the Over to still operate as it does normally and NOT be limited to NORTH rows in its execution.

Regards

 

SM

[Richard Pobi]

All the calculated columns are correct based on their expression. "Count Over Test3", you partition the count on only product without factoring the region column.That is why fruit 8 shows on North and South. "Count Over Test 2", you partition the count based on region and product column. So the count function considers region and product before counting hence the number 4 for friuts. So these Count() OVER ([Region],[Product]) and If([Region]="North",Count() OVER ([Product]),Null) will give you the same result set. What you have done is correct. "Count Over Test 3" and "Count Over Test" can not give you the same result set because you did not factor in the region in both expression.

You can get the output you are trying to get, with the below expression

Case when [Region]='North' then"Count Over Test3"

End

[Stephen Mackenzie 2]

Because Region is not in the over expression, why does it give me 4 and not 8.  I would expect the number 8 for fruit, and the IF statement only giving me an output on the North region.  I don't expect the IF statement to take the region into account in the Over.

 

 

 

Both Count Over Test and Count Over Test 3 use the same Over logic which does not use Region

[Richard Pobi]

If([Region]="North",Count() OVER ([Product]),Null) with the above expression, you are restricting your dataset to analysis data coming from North, so fruit will show 4 not 8. In order words, you are saying if Region is equal to North then count the number of fruit that falls under North. North and South will show fruit as 8. It's like using limit data expression, and hardcoding [Region]='North.' That will show you only data coming from North.