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.

GrantSmith

Hi @MattLynn

It's because there's no data for Domo to evaluate in your pivot table to even apply a coalesce on. What you can do is under the General Properties of your pivot table you can check the "Fill Empty Data Cells With 0" option to tell Domo to display 0 instead of blanks in your pivot table.

GrantSmith

Dang @MarkSnodgrass I thought I was faster than you!

His solution is the better one since you're wanting to calculate the difference and don't always want 0 displayed for your null values. You will need to come up with a cross product to determine all possible year + client + state combinations, left join your original dataset to this new combination dataset, do some aggregation to sum them all and then use the resulting dataset in your pivot table. As Mark mention you can use the window function as well.

Until next time @MarkSnodgrass ...

GrantSmith

Here's some code you can copy and paste into a Magic ETL 2.0 which I think highlights Mark's solution:

{"contentType":"domo/dataflow-actions","data":[{"name":"Sample Sales","id":"7f021ba1-313c-4d31-b381-45cb744bfd89","type":"LoadFromVault","gui":{"x":120,"y":396,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"61aba6af-f101-44f2-bf2a-522091d35964","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Seller","id":"36b81342-dd67-4086-a3ab-1807c9c5af51","type":"SelectValues","gui":{"x":276,"y":60},"dependsOn":["7f021ba1-313c-4d31-b381-45cb744bfd89"],"removeByDefault":false,"notes":[],"fields":[{"name":"Seller"}]},{"name":"State","id":"eef1ac69-1e81-4d18-bec3-08d00959d242","type":"SelectValues","gui":{"x":276,"y":168},"dependsOn":["7f021ba1-313c-4d31-b381-45cb744bfd89"],"removeByDefault":false,"notes":[],"fields":[{"name":"State"}]},{"name":"Select Columns 2","id":"e404c1d7-222d-4e48-b94e-cff1166b6d7a","type":"SelectValues","gui":{"x":276,"y":276},"dependsOn":["7f021ba1-313c-4d31-b381-45cb744bfd89"],"removeByDefault":false,"notes":[],"fields":[{"name":"Year"}]},{"name":"Remove Duplicates","id":"6a924267-addd-49e6-ae83-0475d56c7ff6","type":"Unique","gui":{"x":384,"y":60},"dependsOn":["36b81342-dd67-4086-a3ab-1807c9c5af51"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"fields":[{"name":"Seller","caseInsensitive":false}]},{"name":"Remove Duplicates 1","id":"4025c6a3-3031-4bf9-926b-ed4b38cefaeb","type":"Unique","gui":{"x":384,"y":168},"dependsOn":["eef1ac69-1e81-4d18-bec3-08d00959d242"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"fields":[{"name":"State","caseInsensitive":false}]},{"name":"Remove Duplicates 2","id":"73ca55b0-88e5-40a4-bc44-e064bf4cf4c0","type":"Unique","gui":{"x":384,"y":276},"dependsOn":["e404c1d7-222d-4e48-b94e-cff1166b6d7a"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"fields":[{"name":"Year","caseInsensitive":false}]},{"name":"Add Constants","id":"0b0efb01-f6ec-4552-b9e0-857d1e0ee8ad","type":"Constant","gui":{"x":504,"y":60},"dependsOn":["6a924267-addd-49e6-ae83-0475d56c7ff6"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Add Constants 1","id":"c7c9bd08-8115-4dd0-b87d-aeb182f5ccfa","type":"Constant","gui":{"x":504,"y":168},"dependsOn":["4025c6a3-3031-4bf9-926b-ed4b38cefaeb"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Add Constants 2","id":"33f61234-b1cd-418e-a879-c06f6d4696d9","type":"Constant","gui":{"x":504,"y":276},"dependsOn":["73ca55b0-88e5-40a4-bc44-e064bf4cf4c0"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Join Data","id":"f32e7ae9-63c4-4012-ad52-5702582d0d59","type":"MergeJoin","gui":{"x":648,"y":108},"dependsOn":["0b0efb01-f6ec-4552-b9e0-857d1e0ee8ad","c7c9bd08-8115-4dd0-b87d-aeb182f5ccfa"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"0b0efb01-f6ec-4552-b9e0-857d1e0ee8ad","step2":"c7c9bd08-8115-4dd0-b87d-aeb182f5ccfa","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[],"schemaModification2":[{"name":"Join Column","rename":"Add Constants 1.Join Column","remove":true}],"partitioningInputId":""},{"name":"Join Data 1","id":"4b64671c-dac9-47a3-903f-e8409c14800f","type":"MergeJoin","gui":{"x":768,"y":180},"dependsOn":["f32e7ae9-63c4-4012-ad52-5702582d0d59","33f61234-b1cd-418e-a879-c06f6d4696d9"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"f32e7ae9-63c4-4012-ad52-5702582d0d59","step2":"33f61234-b1cd-418e-a879-c06f6d4696d9","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[],"schemaModification2":[{"name":"Join Column","rename":"Add Constants 1.Join Column","remove":true}],"partitioningInputId":""},{"name":"Join Data 2","id":"b9842fd1-dc9a-4c12-bf90-c42da6522564","type":"MergeJoin","gui":{"x":888,"y":276},"dependsOn":["7f021ba1-313c-4d31-b381-45cb744bfd89","4b64671c-dac9-47a3-903f-e8409c14800f"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"4b64671c-dac9-47a3-903f-e8409c14800f","step2":"7f021ba1-313c-4d31-b381-45cb744bfd89","keys1":["Seller","State","Year"],"keys2":["Seller","State","Year"],"schemaModification1":[],"schemaModification2":[{"name":"Seller","rename":"","remove":true},{"name":"State","rename":"","remove":true},{"name":"Year","rename":"","remove":true}],"partitioningInputId":""},{"name":"Group By","id":"a07647a4-18b2-493a-8ec9-08c45295e1d3","type":"GroupBy","gui":{"x":996,"y":276},"dependsOn":["b9842fd1-dc9a-4c12-bf90-c42da6522564"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Seller"},{"name":"State"},{"name":"Year"}],"partitionedAggregation":false,"fields":[{"name":"Sales","source":"Sales","type":"SUM"}]},{"name":"Rank & Window","id":"6bacc081-dd16-4945-923c-afb6f7daf9a9","type":"WindowAction","gui":{"x":1116,"y":276},"dependsOn":["a07647a4-18b2-493a-8ec9-08c45295e1d3"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Last Year","operation":{"type":"OFFSET","operationType":"LAG","column":"Sales","amount":"1"}}],"orderRules":[{"column":"Year","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"Seller","caseSensitive":false},{"column":"State","caseSensitive":false}]},{"name":"Add Formula","id":"5a83a3b4-4670-4193-9cea-4c7f8d77f89d","type":"ExpressionEvaluator","gui":{"x":1224,"y":276},"dependsOn":["6bacc081-dd16-4945-923c-afb6f7daf9a9"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"Last Year Diff","expression":"`Sales` - `Last Year`","settings":null}]},{"name":"Output DataSet","id":"f0f96326-d165-492b-ad3a-9c435d190911","type":"PublishToVault","gui":{"x":1332,"y":276},"dependsOn":["5a83a3b4-4670-4193-9cea-4c7f8d77f89d"],"removeByDefault":false,"notes":[],"dataSource":{},"partitionIdColumns":[]}]}

This was using your sample dataset you provided on your other question. It should end up looking something like:

jaeW_at_Onyx

@GrantSmith it feels like you're overcomplicating this.

if it were me, i'd try writing CASE statements

CASE

WHEN numerator is not null and denominator is not null then numertor / denominator

When denominator is not null then 100

else null

END

MattLynn

Thanks for the comments everyone! I'll need to revisit this next week since I've been pulled into different project. I like the suggestions given and will try it out in ETL. @GrantSmith I did use the zero out all blanks method but like you mentioned it isn't quite what I need since they should be filled with the formula results (not to mention that I still had blanks with the method on, which is probably a bug I need to report).

@jaeW_at_Onyx Do you mean to use that case statement as a beast mode or in ETL?

Thanks again, everyone. I'll give an update when I hop back on this project!