Beast mode difference shows blank even when using coalesce

MattLynn

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

MarkSnodgrass

This is where I would turn to using Magic ETL. You can use the Rank & Window tile to easily do the Lag and partitions. You could also join it again itself to create a list of sales people and list of years so that there are no gaps, and then do the rank and window work.