Exclude Table Calculations For Null Columns

@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

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

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

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.

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

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.