Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Exclude Table Calculations For Null Columns

J
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

mikhailxu
Rank
Userlevel 3

Hey @josmeye,


This may work!



M

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.


Thanks,

Manoj

A

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.

rufus
Rank
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 help.looker.com and we can take a look to see if there’s anything we can do.

A

@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.


J
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
Rank
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:


pivot_index(${yoy_revenue_change},1)


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.

R

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.

S
Rank

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.

Dawid
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?

Dawid
rufus
Rank
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:


20%20PM


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

DaanIF
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.

Reply


Terms of Service