Use of “In” and “Where” operators in Webi

---

This week I want to demonstrate the correct use of “In” and “Where” operators in Webi as their syntax in Webi is somewhat different from the ones used in MS Excel and SQL.

Take the table below as an example. We want to calculate the maximum “Sales Volume” for all “Product IDs”.

Here is the formula for calculating the maximum “Sales Volume” for all “Product ID”s. Take note that the parenthesis for Max needs to surrounds the entire argument in order for the “In” and “Where” operators to correctly take effect.

=Max ([Sales Volume] In ([Product ID]))

This context uses the “In” operator to specify Input. This assumes that the calculation only addresses the [Sales Volume] values when locating the maximum amount.

There is no output context defined, so the calculation uses the default output context of the entire report, because it was placed at the report level (i.e. in a blank cell) rather that in a table. The screen shot below depicts the maximum “Sales Volume” for “Product ID”s upon inserting the aforementioned formula into a frees-standing blank cell

The next calculation is for calculating the Maximum Sales volume for a Certain “Customer ID”.

=Max ([Sales Volume] In ([Product ID]) Where ([Customer ID] ="SS004"))

The formula above specifies the max “Sales Volume” only for “Customer ID”s equal to “SS004”
Once again, the parenthesis “()” for Max should surround the entire argument. Also, the parenthesis which appear after “In” and “Where” need to surround their respective arguments.

Here is the desired output

---