BusinessObjects Board

IF versus WHERE - difference

Hello BOBJ Experts!

I noticed that IF and WHERE statements can produce different outputs or fail when used within aggregation function. Example:

=Average([Sum of Amount] Where ([Country] = "UK"))
=Average(If [Country] = "UK" Then [Sum of Amount])

Does anyone know in what circumstances the above will not produce the same results? Or one will work while another won’t? What is, in general, underlying logic difference for those?

Regards,
Q.

1 Like

did you try to calculate/compare these without the average?
average on detail-row-level will be different to average on a aggregated level.

I am rather interested in knowledge of what is the difference in under-the-hood calculation logic between those two notations/approaches.

Q.

if you remove the average and then insert the variable into a report or a table,
you can see that "… Where ([Country] = … " is working/calculating without dependency to the other data shown in the table/report and can be used standalone → the calculation-context is based on all the data with “Country = UK”

the if-statement is working with every single line in the report/table and this may result e.g. in a “MULTIVALUE”-error if you use it standalone.

the average() is hiding the problem/details, so it is helpful to always simplify a formula to see what is going on in background.

1 Like

Thanks,

Do I understand correctly that Where is generally context-independent (unless I define it explicitly using eg. In)? Or it defines its own context somehow? I was unable to find any good resources how exactly Where works with contexts. For example, what would the hands-on difference between the below be
=(Average([Sum of Amount] Where ([Country] = "UK")))
=(Average([Sum of Amount]) Where ([Country] = "UK"))
Syntax suggests I used it in input vs output context, but what that would mean for ‘Where’ statement?

Q.

every formula is dependent on the context in which it is used.

you can check what dependency to the existing values in the table are affecting the result, when adding your formula to a existing table and then adding/removing columns of the table step by step until the formula is the only content in the table.

the moment the result is no longer correct, you can modify the context with “forall”, “foreach”, “in” and parenthesis to add/remove/specify the basis of the calculation, even if the dependent fields are not included in the table.