# Filtering in Visualizations using Table Calculations (3.28+)

#1

As of version 3.28, it is possible to filter a visualization using table calculations. This can be done by creating a table calculation that returns Yes or No and then selecting Hide "No"s from Visualization in the column’s gear dropdown.

For example, let’s say you have the following table showing Order Count and Percent of Total

You may want to only see ages with a Percent of Total smaller than 10%, to see which age groups you need to focus on. To do this, you can create a table calculation that tests for whether Percent of Total is less than 10%, like so:

``````\${percent_of_total} < 0.1
``````

Creating this data table:

You can then hide the rows where Percent of Total less than 10% is No by selecting Hide "No"s from Visualization in the column’s gear dropdown:

When you open the visualization tab to any chart type, you can now see only rows where Percent of Total less than 10% is Yes. This will also hide the Percent of Total less than 10% column itself.

Tip: How to filter a table calculation
Exclude Table Calculations For Null Columns
How to limit a visualization using table calculations
How to filter a measure but still show totals
Standard Deviation and simple time series outlier detection using Table Calculations
Filtering rows by measure on all of the pivoted values
Looker 3.28 Release Notes
Average of Last 4 weeks
Building XmR charts (Shewhart's control charts)
Filtering causing me loss of data
Calculating percent of previous and percent change with table calculations
How do I count duplicate values in a table?
Excluding the first row in a table of time series data
(Zachary Michel) #2

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

#3

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

(titleistt) #4

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.

#5

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

(Phil Jones) #6

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

(davids) #7

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.

(Carter Moar) #9

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 reach out to support@looker.com and we can get you pointed in the right direction.

(Manoj Pushpanathan) #10

Thanks @lindsey 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

#11

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

(Manoj Pushpanathan) #12

@lindsey

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

Manoj

(Timothy Burke) #13

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?

(Morgan Imel) #14

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!

(Timothy Burke) #15

@Morgan 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!

(Alexander McMurray) #16

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?

(romain.ducarrouge) #17

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.

(Michael King) #18

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?

(Alexander McMurray) #19

@michael.king - 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).

(Nick) #20

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

Filtering by Calculation columns