Filtering causing me loss of data

(Jeremy) #1

Hello,

I’m looking for help in sorting this table.

I want to sort the table so that it shows me any time I peak above 3. I thought it was an easy filter to filter count > 3. Turns out, I lose the rest of data in that row which I need because it’s not all above 3.

In this screen-shot, I’m only interested in row 3 because it has at least one value above my threshold. I want to filter out the other rows but keep the rest of the values in my row (the 1’s and nulls).

Any ideas would be appreciated.

0 Likes

(Ryan Dunlavy) #2

Hello @Michman,

This sounds like a good time to use the pivot-related table calculation functions. The pivot_where function checks if any of the pivoted columns match a certain condition, and then returns an expression if there are any results and null if there are not. Here, we can use the pivot_where function to check for counts greater than 3, and return “No” for any results that are null:

NOT is_null(pivot_where(${verifications.count}>3, yes))

Then, we can filter out the null columns by selecting “Hide No’s From Visualization”. Here is an example of what this would look like on some test data:

Hope this helps!

Best,
Ryan

0 Likes

(Jeremy) #3

Thanks @ryan.dunlavy

I attempted a similar calculation using the max and pivot-row function to accomplish the same outcome. I then selected “Hide No’s From Visualization.” It worked as you described.

The problem is that the data section still show the “No” rows and I’m dealing with tens of thousands of rows of data, maxing out Looker’s row limit. I need a way to reduce the data size, not just the visualization table size.

I hope that makes sense.

0 Likes

(Ryan Dunlavy) #4

Hey @Michman,

Right now table calculations are the only way to filter out rows based on pivoted columns. I’ll let the product team know you’d like to be able to have filters apply to rows with pivots as well.

Ryan

0 Likes