Advance with Assist shares quick solutions to common challenges encountered by the InterWorks Assist on-demand team.

Question: I’m using a table calculation comparing the same month last year, and my view shows blanks due to the date range I have applied. Is there a way to fill in the blanks correctly?

I’ve duplicated the user’s view somewhat using Superstore data. You can see that I’ve filtered to 2017 and 2018 in the view and applied a table calculation for 12 months prior:

The goal is to get all the months filled in, as we know our data goes back to 2015. The key issue with this is that we’ve “filtered” out years prior to 2017. We need to keep that data in the view but not display it. That’s where the HIDE functionality comes into play for us. Using a calculation, we can create a calculated field that keeps our desired view intact, as well as the data we need for the table calculation.

Applying the Table Calculation

Let’s walk through how to apply it.

For my calculation, I simply want to keep 2016 forward. For that, my calculation looks like the below. You could make this more dynamic with your date filters, etc. I’m just showing the concept here:

Next, we add this to the front of the columns in this view, so we see that 2015-2016 has a HIDE header and 2017-2018 has SHOW:

Now, if you right-click the HIDE header, you’ll see the Hide menu option. Click this to visually hide data prior to 2017, but the data for the table calculation will remain intact as needed:

Once we’ve hidden our unwanted columns, our view has the desired columns filled in for all of 2017 instead of the blanks they were initially:

This could be applied in a variety of different ways. The key is that Hide doesn’t filter the data; it only removes it visually. I hope this helps with your Tableau vizzes!