Working with data from an SQL query

Get Help
1

Hi folks,

A client wants a specific report which gives the average visitors per hour per day.
To be able to archief this, i first create a SQL query in Metabase.

This gives me the location, day, hour, total visitors.
Then i can create a second question in Metabase based on the previous created question where I then can summarize based on the average of count.

This is all working without a problem.

The issue is that the client wants to be able to filter that report on values which are not in the SELECT part of the query. And Metabase only gives you the option to filter on columns that are in the SELECT part.

I tried to work with variables like this:
where 1 = 1
[[AND {{datum}}]]
[[AND {{recyclagepark}}]]
[[AND {{cluster}}]]
[[AND {{herkomst}}]]
[[AND {{bezoekerstype}}]]

but when i then convert it into a modal, it says it's not allowed and that I have to remove it first.

Does anyone have an idea how to acomplish what I want? Is it even possible to do it with Metabase?

If would be nice that i could use this:

image
image1083×1080 59.3 KB

Use the filters that i've created with the variables, and then explore the results with metabase. But if I click now "explore results" then i does not take the filters in to account.

2

Hi @cobalt
You cannot pass-thru filters to nested questions:
https://github.com/metabase/metabase/issues/6449 - upvote by clicking :+1: on the first post
You would have to return all columns needed, which you could then hide, so they are only shown in details view. This could be done with a view or Models.

3

Upvoted.

I don't think your solution would work as it is an aggregate query.
In that case every column in the 'select' needs to be in the 'group by' as well, which makes the result incorrect.

(Example: 10 visitors on day 1, if i added visitortype as a column, it would split those 10 in 5 for visitortype A, and 5 for visitortype B. If we would then calc the average, we would have a different and incorrect outcome)