NULL value shown on filter regardless

Question

I have created a calculated field to only show week numbers for the latest four weeks from today. I called it latest_4_weeks with the following definition:

if (([week_number] <= datepart('week',today())) and ([week_number] > datepart('week',today()) -4)) then [week_number]
end

When I add this field to my columns an unwanted NULL value shows up. I still haven't been able to tell the Tableau to ignore all the null values using IFNULL(), etc.

And when I manually filter out the NULL value, the filter no longer works as it should. Since it will now apply the current values and all other values that relate to next week will be ignored.

How can I effectively remove NULL in my calculated field?

You can just check NULL and Exclude only that in the same filter box so all new values will also be retained. — Jil Jung Juk
So there is no way to write in the calculated field to ignore the null values @JilJungJuk? — disasterkid
If I understand what you are after from a visual perspective I believe an else block with a value of '' will do the trick. This will create a blank value rather than a null and will therefore appear empty on your view. — smb

Jil Jung Juk Jil Jung Juk · Accepted Answer · 2017-10-24T08:49:22

The reason the NULL's are being created is because your don't have a else block that assigns value when the condition is not Satisfied. So add a else block

e.g IF(COND) THEN A ELSE B END.

Or. You can also exclude NULL if you dont want to assign an else block in the same filter block by checking the Exclude option on bottom right corner.

NULL value shown on filter regardless

1 Answers