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


#1

The content of this article has been moved to Looker Docs here.


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
Excluding the first row in a table of time series data
Average of Last 4 weeks
Filtering causing me loss of data
Filtering rows by measure on all of the pivoted values
How do I count duplicate values in a table?
Building XmR charts (Shewhart's control charts)
Looker 3.28 Release Notes
Calculating percent of previous and percent change with table calculations
(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]


(Parijat Talkad) #8

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