How calculate ratio based on two tables that can't be joined

Get Help
1

Hi,

I am trying to compute a ratio based on values of two distinct tables which can’t be joined. Is there a way to do this ? I was thinking that maybe it was possible to create 2 different questions which both output a number e.g. question1 = 2 and question2 = 4 and then do a third question which is equal to the ratio of the two first ones i.e. question1 / question2 = 50%.

Is there a way to do that in Metabase ?

Thank you very much in advance !

2

Hi @anton1
Sounds like Sub-Query would be a solution for that:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#using-an-existing-question-as-a-sub-query

Depending on how you intended to implement what you’re asking for, it sounds like this request would make it possible to do what you’re asking for too:
https://github.com/metabase/metabase/issues/10273 - upvote by clicking :+1: on the first post

3

Hi flamber,

Thank you very much for your response. And is there a way to apply a filter in the sub-query ? In fact, I would like to implement a date filter on question 1 (from the example in my first post). Is there a way to this ?

Many thanks

4

@anton1 Not officially, but read the comments for a hacky workaround:
https://github.com/metabase/metabase/issues/6449 - upvote by clicking :+1: on the first post

5

I had a similar problem, but even more complicated because I need to apply several filters, including a date range filter, to two different tables. Luckily I found a workaround:

  1. Make a CTE to define parameters, which you later apply to both tables, like so:
WITH parameters AS(
    SELECT 
        filter1
        , filter2
        , MIN(date_range_filter) AS daterange_start
        , MAX(date_range_filter) AS daterange_end
    FROM `your_project.your_table_A`
    WHERE {{filter1}}
      AND {{filter2}}
      AND {{date_range_filter}}
   GROUP BY filter1, filter2
)
  1. Next get the data from tables A and B and put the values, which are selected by the users through the dasbhoard filters:
, get_filtered_data_from_table_A AS(
    SELECT 
        this / that AS result A
    FROM `your_project.your_table_A` s
    JOIN parameters p
        ON s.filter1 = p.filter1
        AND s.filter1 = p.filter1
        AND DATE(s.starts_at) >= DATE(p.date_range_starts) 
        AND DATE(s.starts_at) < DATE(p.date_range_ends)

, get_filtered_data_from_table_b AS(
same here
)
  1. Calculate whatever you need. For me it was like:
SELECT resultA / resultB
FROM get_filtered_data_from_table_A
CROSS JOIN get_filtered_data_from_table_B

Maybe you can work with a subquery here, but at least it works, even with more complicated filters. hope this helps.