Search code examples
filtercalculated-columnscognos-10

Cognos 10 Filtering a calculated measure

Using a Crosstab dimensional report I having issues filtering out a specific value that I am getting from my calculated measure:

I am using the following calculated measure in Reporting Studio to get the required output..

 ROUND(([A1_SCHEDQTY]+[B1_SCHEDQTY]) / ([A1_QTY]+[B1_QTY]) * 100, 1)

However, in a few cases I am getting an output of '/0' I am unable to filter this out. I have tried using an IF statement, but I believe my syntax is incorrect.

IF (ROUND(([A1_SCHEDQTY]+[B1_SCHEDQTY]) / ([A1_QTY]+[B1_QTY]) * 100, 1) = '/0') THEN NULL ELSE ROUND(([A1_SCHEDQTY]+[B1_SCHEDQTY]) / ([A1_QTY]+[B1_QTY]) * 100, 1)

Any advice?

Solution

  • You need to check the divisor of the equation for 0 instead of checking the entire equation for '/0'. If you check the entire equation, the divide by 0 still takes place.

    Try this:

    IF ([A1_QTY]+[B1_QTY] = 0) THEN (NULL) ELSE (ROUND(([A1_SCHEDQTY]+[B1_SCHEDQTY]) / ([A1_QTY]+[B1_QTY]) * 100, 1))