@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 ifnull()
or 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.
Beast modes:
Filings: 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)
Difference: 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 COALESCE
with 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 count
, etc.
I'm probably missing something obvious, but I'm not seeing it. Any help is greatly appreciated, thanks.