Perhaps it was a part of the original data, but the last entry for August for Category 18 in your workbook that you attached wasn't there as a valid row in your attached workbook. So, I'm not sure how Tableau represented a row of data that wasn't in the data based on your screenshot?
Regardless, I brought out your data and added that row back in and left the value as Null for that category in August. If you follow the screenshots below you should get the result you're seeking.
HOWEVER, if you're indicating that Tableau should recognize that row of data when it's not there to begin with, then that's not physically possible...
We should be seeing 4 rows of data with one Value being null in your data. All I was seeing is just the first 3 rows in your workbook.
Using your IFNULL calculation, you'd get the following when placed on Marks Card:
Drag your Calculation to Filters and set it to use maximum value of 123 to see which date/category where/when a product wasn't sold:
Hi, that's correct, the dataset will not have this row to start with, this is where the difficulty lies, I could somehow tweak the SQL behind the dataset to add in the row I guess but I really didn't want to have to do that and show users how to be in "self-service" mode and get it done without IT intervention
How Tableau displays the "empty row" is in the following option:
- Analysis menu
- Table layout
- Show empty rows
- Show empty columns
Given this I can understand the behaviour for Tableau not being able to filter what is physically not there, but I would have some kind of wish that it could since Tableau is able to detect an empty row and even assign a physical display value when a calculated measure is created
Is there any way that you can think of to try to "blend" the same source ? i'll try to explain what's in my mind:
Take source 1 and connect to the full dataset
Take source 1 again and create a new dataset, but this time hide all columns except the category column so to get the "list" of all possible category values
Somehow "blend" the data and use category as the "main" information and filter on a null value (with a calculated measure or something)
Just a thought, but if it's not possible, then it's not...i'll have to tweak the underlying dataset somehow with the users
Thank you for replying!