Exclude Table Calculations For Null Columns

Userlevel 1

Created a calculated column for WoW comparisons. Is there a way to exclude instances where all values are null in the calculated column for the last column where there is nothing to compare against?

12 replies

Userlevel 3

I believe this is easily reached with a simpel filter on the column that must NOT be only NULL values, and set it to: IS NOT NULL.

That way, it will only show columns with at least one value that IS NOT NULL.

I think this mainly works because the WHERE NOT <field> IS NULL clause is applied BEFORE the pivoting takes place. So any column with only NULL’s is deleted this way.

Note: It will also take away all rows that only have NULL values. So you have to be sure that there will be a value for each row in at least one of the other columns.

Userlevel 3

@Dawid_Nawrot not sure what your exact use case is but I achieved something like this by switching my pivoted dimensions, then hiding the 'No’s from the visualization and transposing the table in the viz:

Here are my calculation formulas:

YOY - ${order_items.total_gross_margin} - offset(${order_items.total_gross_margin},-1)

YOY Is Not Null - NOT is_null(${yoy})

Transposing is an option that will be available in our new table viz that is currently in beta. More details on that here - Table Next Options

Userlevel 7
Badge +1

What if we have dynamic number of pivot columns? How can we hide just the first one that will always be null?

Hi @Renaud, are you using a string in the table calculation? If you are, then there is not difference in the result of the table cal, but then, the “Hide No From Visualisation” wont appear. In order to have it in the option you should use a yes, no as a value.

Recently tried to deploy this solution for a similar problem but when I go to filter out the “No” by clicking on the Yes/No table calculation I’ve created it doesn’t even give me the option anymore… I’m running Looker 4.18.

Userlevel 3

@asjlyle, thanks for providing an example. A solution in this case would be to add another calculation that uses pivot_index to return the value in one of the year columns:


Then we can hide the original calculation from the visualization and just return the value for the year that isn’t null:

Moving the calculation into the model as per Joseph’s example also works.

Userlevel 1

The workaround we found was to include the WoW calculation in the actual data itself which took some sql work to get it to work; it would provide WoW for every week; we then used a table calculation to only choose the last week and we hid the actual WoW columns that we included.

Not an ideal solution, but it worked. We also used a “Week” title column because we were including weeks and months in the same report

pivot_where(${ops_3p_wbr_ooc_visits_details_subt.date_type} = “1-WEEK6”, ${ops_3p_wbr_ooc_visits_details_subt.previous_lost_orders})

@rufus Here is my example. I have a table which calculates YoY change. The 2 year values are pivots and a YoY calculation is calculated using pivot_offset. Therefore the YoY Change values only exist in one of the colums and the other column values are null. In this case, I cannot click on the gear and select ‘hide from visualization’ because this would hide both columns showing YoY change, the one that has the values and the one that contains NULL values. I want to hide the column that contains all null values only.

Userlevel 3

@asjlyle, do you have an example with the type of fields in your report? Does your data include a pivot? There should be a ‘Hide from Visualization’ option in the gear dropdown in each column. Feel free to share your example here or visit and we can take a look to see if there’s anything we can do.

Hi There. Does anyone have a solution or workaround to this issue? There seems to still be no option to hide a column when the column contains only NULL values.

I have the same question.

@mikhailxu That solution works for hiding particular rows but not the columns. I want to hide columns that have all null values.



Userlevel 3

Hey @josmeye,

This may work!