Filter out dates in one worksheet if they are not in other worksheet in Tableau

0
votes

enter image description here(EDITED FOR CLARITY)

I have a dashboard that compares a company selected by the user (Company A) to another group (Company B). One worksheet is their company (Company A), and the second worksheet is any other company selected by the user (Company B). These metrics are compared over time in a bar chart, however, some companies are missing data from some years. This means that one set of bar charts will have more bars than the other, creating an uneven looking visualization. Is there a way to filter out data points for the comparison companies (Company B) if there is no data in that year for the user's company (Company A)?

So the issue here is that Company B only has Var1, Var2, and Var3 for years 2012 and 2014, compared to company A which has all of the vars. The visualization works when there are all vars for the same years, but it breaks down when a Company is missing some of this information. I want it to be usable with a filter that the user can use from a drop down list, so I can't just hide things manually.

1
tableau-api
does your "date" field has the date type as string or actually date? – astro11
From a visualisation point of view, why would you want to hide the data you've got for company A? It still adds to the graph and hiding them might lead to misinterpretation, ie. when you hide 5 years because CompB doesn't provide data, it might look like a hughe increase from one year to another while in reality it's a usual growth over 5 years. – Alexander
Thank you for your comments and your help! My date field is currently numeric, as it is just the 4 digit year. I will experiment with changing it to date and get back to this thread. Also, Alexander, this is a good point. I want to hide the data for purely visual reasons, because when I have this in a dashboard, and a user picks an institution that doesn't have data for all years, one half of the dashboard shrinks, making the viz uneven. – lbug
What Alexander said is exactly what I had in mind when I answered with the "Show Missing Values" option, this way you won't be omitting any information that you have (by excluding bars from Company A), and both charts will have the same size/number of months. – astro11

1 Answers

1
votes

Instead of omitting values from one chart to make it "visually equivalent", you should show the missing values.

If your date field has the data type as Date - and not String / Numeric - you should be able to right-click the date axis and choose the "Show Missing Values" option, as both images below show:

Some months are clearly missing in this image

And now they're showing

If your field is set to String / Numeric, you'll need to change it either manually in the dataset or in Tableau by right-clicking and changing it to Date or Date/Time.

In the case you only have the year as value (e.g. 2012; 2013; 2014) - thus making it Numeric - you might want to use this workaround to create a new Date field:

DATE("1/1/" + STR(RIGHT( [Year] , 4)))