Null values in date fields

grace

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!

MarkSnodgrass

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.

MarkSnodgrass

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.