Exclude Table Calculations For Null Columns

open
low_priority
reply
(Joseph) #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?

0 Likes

(Dirty Looker) #2

Hey @josmeye,

This may work!

0 Likes

(Manoj Pushpanathan) #3

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

0 Likes

(Annabel) #4

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.

0 Likes

(rufus) #5

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

0 Likes

(Annabel) #6

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

0 Likes

(Joseph) #7

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})

0 Likes

(rufus) #8

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

0 Likes

(Renaud) #9

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.

0 Likes

(Sara Guzman) #10

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.

0 Likes

(Dawid) #11

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

0 Likes