Creating a Calendar in Tableau

1
votes

So I have a SQL data table that is nothing but a calendar for the next 200 years. I join this to meaningful 'task data' by the date field on the calendar table matching the task completion date of the task table. I am using that to build a calendar in Tableau and it looks great... at first:

enter image description here

That image above contains no filtering on the owner and shows all the days, even when the task count is 0.

But when I filter for an individual who has no tasks to count, the days disappear that are null:

enter image description here

If I hover over the white square, nothing is displayed in the detail drop down and notice that I've lost some days... I went from 8/5 to 8/9.

I understand why this is happening (because the data simply doesn't exist in the filtered view), but there has got to be a way to still allow the day to display, but simply show a 0 for the task count instead of a null. I've made a custom query and used isnull on both the task_Id and the Task_Owner but I still get the same view. Other than creating dummy data in the db as a placeholder (which I am against) I am not sure how to achieve this.

I'm hoping someone out there has struggled and succeeded with this before and can help me out. I feel like the zn(lookup) function in Tableau may be helpful but I can't seem to apply it in a way that accomplishes my goal.

Any help is greatly appreciated.

2
sql-servercalendartableau-api
The only way to do this is to change your dataset to include all days for all users regardless of if they have a task or not. The reason is that NULL is non-existent, so no number of calculations ISNULL, etc are going to be evaluated for that particular "date". The reason you see it, is because Tableau presents it (in this case a blank square) but there is still no data behind it. - Nick
To add one more bit of clarity. When you are using ISNULL, LOOKUP, etc - you are effectively trying to create a new record of data, which Tableau cannot do. - Nick
I am in agreement of everything you said. I am just going to have bite the bullet so to speak, and create a line item for each day for each user. Thank you for taking a look and replying. - user3486773

2 Answers

0
votes

You are in luck.

For most datatypes, Tableau discovers the values for the dimensions from the query results. So in general, it won't know to leave a column for an absent value. But for dates, it knows about the possible values so can behave a little nicer.

That is, it can know that there should be a column for Sunday even though there were no data rows with that value.

So for your WEEKDAY(Date) field on the column shelf, right click and choose "Show missing values". You should see your missing columns. The same trick works for histogram bins as well. You should almost always check that option for histograms.

This can pad out your rows and columns to complete the range, but doesn't get your day of month showing. You can fix that using index() if you set the table calc partitioning correctly.

0
votes

Have you tried this:

 ifnull([Task Count],0)

This would change any nulls to zeros.

Or save typing 6 whole characters and use

ZN([Task Count])

for the same effect