NULL Functions | ZN, IFNULL, ISNULL |

[@toddpashak]

You've quickly become one of my fav Tableau channels, you explain clearly and provide excellent examples. Thank you so much for sharing your knowledge in a way that is understandable and relevant for real world/work applications.

[@DataWithBaraa]

@@toddpashak I am very honored to be able to help you ❤️❤️❤️ thanks for the kind words

[@manlikeMrA]

To be honest and in my opinion, Baraa is the most underrated Tech Trainer on RUclips. He should have at least 10 million subscribers by now. And I'm going to do my part in ensuring that. And i urge us all to do the same, it's the least we can do to support this great man.
This is what I intend to do. As soon as I'm done with this series (playlist), I'll complete one project, and post on my LinkedIn and Twitter, tagging Baraa's LinkedIn and RUclips handles, this should drive engagement for him.
Thank you for going all out for your audience Baraa ❤❤

[@DataWithBaraa]

@@manlikeMrA omg this is very beautiful to read, your support means a lot and very generous of you. I can’t thank you enough, and only with this kind of support I can reach wonderful people like you ❤️❤️❤️

[@abhijitnehere8258]

This is an excellent explanation and example.

[@masudkamrul5949]

Hi, I wanted to if is there any way to see the NULL value from all the column in one table at once instead of checking one by one column.

[@sowmya6471]

Easily understood the concept

[@DataWithBaraa]

Happy to hear 🙂

[@MallaiahThagulla]

You are always making Maria sad...😉

[@DataWithBaraa]

@@MallaiahThagulla 😁

[@manlikeMrA]

I have one question though:
I was considering a possibility where if the age for instance, is null, we replace it with the average age (based on use case) using a formula like IFNULL([customer age], AVG([customer age]) or something following similar logic., but it either threw error (cannot mix aggregate with non-aggregate arguments) or didn't return expected output.
I have tried different alternatives, but none worked.
Do you have any recommendation or workaround?

[@masudkamrul5949]

I tried this too but did not work. I want to know this as well

[@ghazaliyat368]

@@masudkamrul5949 In Tableau, the error you're encountering happens because it doesn't allow mixing aggregate functions (like `AVG`) with non-aggregated fields (like `[customer age]`) directly in conditional statements. To achieve the desired result-replacing `NULL` values with the average age-you can use a **Level of Detail (LOD) expression** to calculate the average age separately and then apply it to the `IFNULL` statement.
Here’s how you can do it:
1. **Create a calculated field for the Average Age**:
First, create a calculated field that calculates the average age across all records. This will allow you to use a single value for replacement, avoiding the aggregation error.
```tableau
{FIXED : AVG([customer age])}
```
Name this field something like `Avg Age`.
2. **Use the Average Age in an IFNULL Calculation**:
Now, create a new calculated field that uses `IFNULL` to replace any `NULL` values in `[customer age]` with this `Avg Age`.
```tableau
IFNULL([customer age], [Avg Age])
```
Name this field something like `Age with Avg Replacement`.
### Explanation:
- `{FIXED : AVG([customer age])}` calculates the overall average of `[customer age]` without any filtering, grouping, or aggregation error.
- `IFNULL([customer age], [Avg Age])` checks if `[customer age]` is `NULL` for each record and replaces it with the calculated `Avg Age` if it is.
This workaround allows you to substitute `NULL` values with the average age without mixing aggregate and non-aggregate fields in a single calculation.