Conditional counts

How do I count only rows with dates and ignore rows with null?
I used count(isNotNull({date})) but only returned the counts for all rows.

I also used count(if(date, isnotnull) but returned error.
Please help.

I created a calculated field ifelse(isNotNull(Date), 1, 0), then use Sum as the aggregation. Does this give you what you need?

2 Likes

Thank you @Steph . It didnt work for me unfortunately. Maybe I applied it in a wrong way. I used count(ifelse(isNotNull({date}), 1, 0)) but it returns counts of all the rows including the null rows.

Hi @Temik, you don’t need the count, just the ifelse. That will set a value of 1 or 0 for every row. Then when you sum that column you will have the right number.

Thank you @Steph. ifelse ( coalesce ( {date}, β€˜β€™ ) = β€˜β€™, 0, 1 ) worked.

1 Like