Null values in date fields

Hi all - running into a simple count issue here wanting to count all Invoice IDs where the date fields discarded_at and archived_at have no value.

Summing invoice amounts with this syntax works -

sum (case when discarded_at is NULL and archived_at is NULL then Invoice Amount (cents)/100 else 0 END)

When I try to do the same thing it pulls through 0s for all discarded and archived invoices and will not just count the non-archived/discarded. The below two options have this issue.

Case when discarded_at is NULL and archived_at is NULL then count(invoice_id) else 0 end

COUNT(case when discarded_at is NULL and archived_at is NULL then invoice_id else 0 end)

When I try to opt for a NULL "then" value instead Domo rejects the syntax. Basically I want to completely discount any data lines where there is a value for discarded_at or archived_at.

Case when discarded_at is NULL and archived_at is NULL then count(invoice_id) else NULL end

Oddly, when I try to apply a filter where discarded_at is blank, the whole visualization ssays "no data found"

Thanks!

Best Answer

  • MarkSnodgrass
    Answer ✓

    Instead of this:

    Case when discarded_at is NULL and archived_at is NULL then count(invoice_id) else NULL end
    

    try this instead:

    SUM(Case when discarded_at is NULL and archived_at is NULL then 1 else 0 end)
    

    Using 1's and 0's with SUM is another way to count items instead of using the COUNT function.

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

Answers

  • MarkSnodgrass
    Answer ✓

    Instead of this:

    Case when discarded_at is NULL and archived_at is NULL then count(invoice_id) else NULL end
    

    try this instead:

    SUM(Case when discarded_at is NULL and archived_at is NULL then 1 else 0 end)
    

    Using 1's and 0's with SUM is another way to count items instead of using the COUNT function.

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