RETIRED: Filtering in Visualizations using Table C...

Another good use of this pattern is to remove null values from a visualization which are a product of a table calculation which always returns null for the first row, i.e. percent change or percent of previous.

Here is my visualization and table as is, notice the null value for 2015-08-24:

If I want to remove the Mon 24 point from the vis I can do so by creating a column that returns No when the value is null, and then filtering out the Nos:

NOT is_null(${count_orders_growth})

A similar pattern to this can also be used to filter a measure and still display totals! Check that out here.

I’m able to filter out “no’s” from the visualization. However, when I get the dashboard lookml and copy into a lookml dashboard, it will still show all the x-axis values.

This looks like a bug with getting the Dashboard LookML. Filing this with eng.

I’m seeing the same thing when getting the dash lookml. Can we add this manually to the lookml?

You can add this manually to the lookml with the following parameter:

hidden_points_if_no: [your_table_calc_name]

I am unable to find this feature, does this still feature still exist? If so, the option to Hide “No’s” from visualization is not appearing.

I was able to get the feature to work on Learn using this (someone nonsensical) Look. The feature doesn’t apply to the string No, though, but on false conditions. Table calculations display a true as Yes and false as No.

If you’re not able to get the feature to work based on this example, feel free to visit us at help.looker.com and we can get you pointed in the right direction.

Thanks @lindsey1 this is very helpful.

I have a case where I need to show a particular calculation ONLY under the last column. This calc is finding the difference between the last column and the second last column.I am able to create the calc, but this column is visible under the second last column as well. How do I remove the calc from that second last column alone?

Apologies if this is confusing.

Thanks,
Manoj

@Manoj_Pushpanat Are you using pivots? If so, I think you’ll want to use pivot_index in your table calculation to refer to the pivoted columns. This will cause the table calculation to appear on the very right side of the table (as opposed to under each pivot value).

For example, creating a table calculation that subtracts two pivot columns from each other would look something like:

pivot_index(${view.field},2) - pivot_index(${view.field},1)

This will make that table calc appear all the way to the right to look something like this:

We also have more about using pivot index in table calcs here.

@lindsey1

Yes, I am using Pivots and this solution works perfect! Thank you so much for the help.

Manoj

This is a very useful feature that makes visualizations much more representative of the data!

It appears, though, that it doesn’t work when you’ve got pivots involved. I’ve got an explore where some series contain null values at certain points of day, and others don’t. The nulls show as 0’s in the visualization:

When I choose “Hide 'No’s from Visualization”, the visualization just displays “No Results”.

Am I missing anything, or does this just not behave with pivots?

Hey @TimothyBurke,

This feature will work with pivots, but the yesno value has to be outside of the pivots. The reason for this is that the “hide no’s from visualization” option hides an entire row! So if the yesno value is inside the pivot, you could have a “No” AND a “Yes” at the same time.

For your use case it would be much easier to instead turn off the “Plot Null Values” toggle in the Plot section of the visualization settings!

@Morgan1 thanks! I have been frequently using these types of table calculations to hide null values but this is even better, exactly what I was missing!

If I use this method to filter out rows - will those rows still be counted for the ‘Send If There Are Results’ scheduled alert?

In other words - can I use table calculations to create an anomaly alert system by writing a table calcuation of ‘is_anomaly’ that assigns ‘No’ to all normal rows and ‘Yes’ to anomalous rows and then hiding No’s with the alert set to only send emails when there is data?

Hey @alexanderm yes that is correct!
You can use this approach to create an alert schedule. And you would do that exactly the way you mentioned by using a table calc that will output a boolean yes or no so that we have the “Hide Nos from Visualization” option available.

Perhaps I’m misunderstanding your point, but I’m not seeing this behaviour when trying to schedule a look. I have a calculated field to record the percentage change of a metric from the row immediately below, then another calculated field to produce Yes/No based on the change (e.g. ‘Yes’ if change more than 50%).

I’ve hidden the ‘No’ rows from the visualisation, then scheduled to send an email only if there are results. If all the rows are filtered out by my Boolean calculated field, I would expect no email to be sent - instead I receive an email with a blank table, only the headers and no data. Am I wrong to expect that this use case would not trigger an email?

@michael_king1 - That is also what happened to me.

This post suggests that filtering on a table calculation when creating a schedule is not currently suported and thus cannot be used for anomaly detection (which is both my use case and the one discussed in the post).

I’m using this pattern to trim the first and last rows of a time series because typically the result sets in those rows are incomplete (partial day, week, month etc…)

RETIRED: Filtering in Visualizations using Table Calculations (3.28+)