1 2 Previous Next 15 Replies Latest reply on Sep 20, 2016 6:34 AM by Kyle Boyce

excluding null value from quick filter drop down

Vishal D Sep 16, 2016 10:57 AM

I have a field 'Store Name' for some reason it has one null value.

[Store Name] = [null, ABC, XYZ, TYZ]

How do I exclude the null when I create a quick filter and show that as drop down?

I dont want to select everything but null and create a set or parameter as that will make the list of values static. Our company could buy more stores and I want the list to be dynamic and get populated from datasource.

I tried 'Conditions Tab' while creating quick filter to say formula : Store Name <> null. But it doesnt work.

1. Re: excluding null value from quick filter drop down

Sreekanth Kasaraneni Sep 16, 2016 11:02 AM (in response to Vishal D)

you can filter directly using exclude option in the similar way as screen shot below even if the values are null

you can also do this at data source level by clicking edit data source filters
Like Show 0 Likes(0)

Actions
2. Re: excluding null value from quick filter drop down

Kyle Boyce Sep 16, 2016 1:44 PM (in response to Vishal D)

Put [Store Name] on your Filters card and un-check the Null value. That should have it removed from the visual, Then go to that pill on the filter shelf and click "Show Filter" and make sure your setting are like this:
Like Show 0 Likes(0)

Actions
3. Re: excluding null value from quick filter drop down

Rajeev Pandey Sep 17, 2016 2:37 AM (in response to Vishal D)

Hi Vishal,
There are three way through which you can deal this problem.
1) Add a footnote stating " Please select the Null value along with other Values" because sometimes you cannot remove null because it will filtered the important rows
2) Remove the " Null" from the data source - Right click on data source - Edit data source Filter->Add-:> Store name -> Exclude Null
3) Just Replace the Null value by adding some useful info
Replace( Store Name Dimesnion, NULL , "No Value")
Also make sure your Quick Filter option should show the " Only Relevant Values"
Like Show 0 Likes(0)

Actions
4. Re: excluding null value from quick filter drop down

Vishal D Sep 17, 2016 4:34 PM (in response to Rajeev Pandey)

Rajeev Pandey
Do i have to create calculated field to write this replace formula?

Replace( Store Name Dimesnion, NULL , "No Value")
Also make sure your Quick Filter option should show the " Only Relevant Values"

Where do I write this?

Also only I do 'Only relevant values' what does this option do to 'No Value'?
Like Show 0 Likes(0)

Actions
5. Re: excluding null value from quick filter drop down

Vishal D Sep 17, 2016 4:45 PM (in response to Kyle Boyce)

Kyle Boyce you solution will make the filter with static list of values, I want it to be dyanmic so that it picks new values which come from database.
Like Show 0 Likes(0)

Actions
6. Re: excluding null value from quick filter drop down

Vishal D Sep 17, 2016 4:48 PM (in response to Sreekanth Kasaraneni)

Sreekanth Kasaraneni
I dont think I want to filter at datasource since it will filter other important data due to the joins defined.

I just want to not show it as a option to select in drop down.

I tried excluding the 'null' but it still shows.
Like Show 0 Likes(0)

Actions
7. Re: excluding null value from quick filter drop down

Rajeev Pandey Sep 19, 2016 3:06 AM (in response to Vishal D)

Hi Vishal,
What I am trying to say here, How you can hide the Null Values from the Quick Filter.
The simplest thing you can do Duplicate the dimension and get that in filter exclude the null. get original dimension in filter and get quick filter to show only relevant.

Removing Null Values from Filters | Tableau Software

Note: This will exclude Null from the Quick Filter which will exclude the Null Values from the view.
Like Show 0 Likes(0)

Actions
8. Re: excluding null value from quick filter drop down

Kyle Boyce Sep 19, 2016 4:56 AM (in response to Vishal D)

Group your Null into the "Others" group. That should would work. Then Exclude the "Others" group and it should always pull everything else in (dynamically).
Like Show 0 Likes(0)

Actions
9. Re: excluding null value from quick filter drop down

Vishal D Sep 19, 2016 12:03 PM (in response to Kyle Boyce)

Create a group, added 'null' to 'Others'

Then added that as filter, excluded 'null others' group.

Did show filter but still see the null
Like Show 0 Likes(0)

Actions
10. Re: excluding null value from quick filter drop down

Sreekanth Kasaraneni Sep 19, 2016 12:18 PM (in response to Vishal D)

Right click on the dimension and create set
select only attributes requires and click ok dont select null while creating the set
place the set onto filter shelf
Like Show 0 Likes(0)

Actions
11. Re: excluding null value from quick filter drop down

Vishal D Sep 19, 2016 1:25 PM (in response to Sreekanth Kasaraneni)

Woudnt creating a set make the list static in point of time?

What if more store names are added in database, will the set pick the new stores dynamically?
Like Show 0 Likes(0)

Actions
12. Re: excluding null value from quick filter drop down

Kyle Boyce Sep 19, 2016 1:27 PM (in response to Vishal D)

Vishal D wrote:

Create a group, added 'null' to 'Others'

Then added that as filter, excluded 'null others' group.

Did show filter but still see the null

Did you select Only relevant values?
Like Show 0 Likes(0)

Actions
13. Re: excluding null value from quick filter drop down

Vishal D Sep 19, 2016 2:17 PM (in response to Kyle Boyce)
yes, see attached twbx - sheet 'Null Issue'

I am sure I am missing something silly, shouldn't be this difficult.

Sample Sales.twbx 101.4 KB
Like Show 0 Likes(0)

Actions
14. Re: excluding null value from quick filter drop down

Sreekanth Kasaraneni Sep 19, 2016 11:05 PM (in response to Vishal D)

Yes as we are excluding only null value the rest of the values will be added by default after refresh

What if more store names are added in database, will the set pick the new stores dynamically?
Yes it will update
Like Show 0 Likes(0)

Actions