Beast mode difference shows blank even when using coalesce
@MarkSnodgrass and @GrantSmith, you both helped me with my last question and for the most part, it gets me in the right direction. The problem seems to be that a null value completely nulls the whole result (like in sql), even when using
coalesce(), whereas excel does evaluate it correctly, albeit with its own list of problems.
The expectation is that last year's filings for the second row, under 2021, would show 1 and the difference would be -1 or (1) and filings would show 0. Since the data doesn't really exist, it evaluates to a blank (i guess this is Domo's null?). Where data does exist, the formulas work perfectly.
coalesce(count(`Filing Type`), 0)
Last Year Filings:
coalesce(LAG(count(`Filing Type`)) OVER (PARTITION BY `Client Name`, `Jurisdiction` ORDER BY `Client Name`, `Jurisdiction`, `Year`), 0)
COALESCE(count(`Filing Type`), 0) - COALESCE(LAG(count(`Filing Type`)) OVER (PARTITION BY `Client Name`, `Jurisdiction` ORDER BY `Client Name`, `Jurisdiction`, `Year`), 0)
I've switched out
IFNULL and got the same result. Even without using any of them, the result is similar. I've also tried a
COALESCE nested inside the
count, wrapped around the
I'm probably missing something obvious, but I'm not seeing it. Any help is greatly appreciated, thanks.