It would be very cool if Advanced Filters supported referring to other dimensions available in the explore.
An example would illustrate my request best:
To work around this, I have a dimension that calculates the difference between member created date & order created date.
It would be even cooler to have the option of specifying a raw SQL snippet as a filter.
Then one could do crazy things such as:
For completeness, here’s the full SQL snippet from the screenshot above:
IN SELECT(${returns.customer_id} FROM ${returns} WHERE ${returns.subtotal} > 200 GROUP BY 1)
– any customer who has made a return worth more than $ 200.
– Its impossible to do this filter in the UI, because as soon as I join to the returns table, I lose all orders that don’t have any returns.
Having the ability to do raw sql queries also allows us to put in custom logical groupings of a filter. Currenty the AND | OR option appears automatically, and I can’t change it. Sometimes I really need the other option but I’m limited by the UI
And other crazy stuff that I can’t even imagine yet
In the immortal words of Stan Lee, “With Great Power, Comes Great Responsibility”.
To really kick it to the next level:
- We must recognize this, and be able to limit who can create a raw sql filter, so also add that in the administration panel,
- The query parser must be able to sanitize badly specified or malicious Sql Snippets
- This should be its own filter type, maybe call it sql-snippet and put it right below advanced.
Lastly, if you guys put this feature in. I will forever refer to Looker as Look-even-cool-er