Jump to content
We've recently updated our Privacy Statement, available here. ×

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

Stephen Mackenzie 2

Recommended Posts

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).



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.




Link to comment
Share on other sites

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"


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
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...