Conditional Sum not Working

  • 19 June 2017
  • 2 replies

I’m calculating a conditional sum that looks like this:

type: sum


CASE WHEN ${typ} = 0

THEN ${x} * -1

WHEN ${typ} = 1

THEN ${x} * -1

WHEN ${typ} = 2

THEN (${A} + ${B} + ${C} + ${D}) * -1

WHEN ${typ} = 6

THEN ${y} * -1

WHEN ${typ} = 7

THEN ${y} * -1

END ;;

I’m focused on typ [type] 2. When I compute this sum for a particular row, A B C are all null, and D has a value. The output of this is 0 and I don’t understand why. If I change the formula to just D, then D will populate, however when I compute (A + B + C + D) * -1, the output is 0. I tried wrapping A B C in nullif statements but the result is still 0. A B C aren’t even null, they are 0 in the data so that shouldn’t matter.

Why would this be? Why is D being ignored and the sum is just 0?

Thank you!

2 replies

@jimRL As written above, there may be a NULL value in the SUM(A+B+C+D) that is causing the erroneous output. Using COALESCE() around the SUM may help resolve any issues with NULLs in the sum. For example, you could do the following:




With that you will either use the summed value or, if the sum has a NULL, then your output could be 0 or the D value, respectively.

Userlevel 7

If any of A, B, or C are null, the value being summed would be null, since NULL + 1 evaluates to NULL. Additionally, Looker’s type:sum coerces NULL sums to 0 (after summing, not before). You mentioned you tried using nullif, but I think you actually want the opposite, a coalesce nulls to 0. So:

type: sum
CASE WHEN ${typ} = 2
THEN (COALESCE(${A},0) + COALESCE(${B},0) + COALESCE(${C},0) + COALESCE(${D},0)) * -1
END ;;