Beast mode difference shows blank even when using coalesce

MattLynn
MattLynn Member
in Magic ETL

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

image.png

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.

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach

    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:

    Screen Shot 2021-10-29 at 4.52.52 PM.png


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MattLynn
    MattLynn Member

    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!

Categories