Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Conditional Sum not Working

  • 19 June 2017
  • 2 replies
  • 325 views
jimRL

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


type: sum

sql:

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

jesus.cruz
Rank

@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:


COALESCE(SUM(A+B+C+D), 0)


Or


COALESCE(SUM(A+B+C+D), D)


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.

fabio
Rank
Userlevel 7
Badge

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

sql:

CASE WHEN ${typ} = 2

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

END ;;

Reply


Terms of Service