CODEWITHSUNDEEP
tableau-apicalculated-field
Tony
Tony

Reputation: 9581

Tableau isNull then 0 calculated field

I have my tableau workbook and I'm currently counting by a field called ID - COUNT([Id]) - while this is great, on days with no activity my dashboard doesn't show ANYTHING and I want it to show zero if there was no activity - so I do I change this to count but also replace null with 0 (zero)?

Upvotes: 1

Views: 3433

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11896

First make sure you understand what Count([ID]) does. It returns the number records in the data source that have a non-null value in the column [ID].

Count() never evaluates to null. But if you have no rows at all in your data after filtering, then you'll get an empty result set -- i.e. view data will not have any summary data to show at all - whether null or zero.

Wrapping COUNT() in a call to ISNULL() or ZN() won't help in that case.

The solution is to make sure you have at least one data row per day, even if all other fields besides the date are null. Aggregation functions ignore nulls so padding your data like this should not disturb your results. The simplest way is to make a calendar table that has one row per day with nulls in most columns. Then use a Union to combine the calendar with your original data source. Then Count(ID) will return zero on days where there are no other records besides the calendar entry.

You can also get similar results using data blending, although with a bit more complexity.

Upvotes: 3

Related Questions