HELP! How to create a customizable line chart

Hello, I want to create a line chart based out of following table:

Schema_Name, Table_Name, Count, Day
Schema_X, Table_X1, 100, 1/11/2019
Schema_X, Table_X2, 200, 1/12/2019
Schema_X, Table_X3, 400, 1/13/2019
Schema_Y, Table_Y1, 500, 1/11/2019
Schema_Y, Table_Y2, 600, 1/12/2019
Schema_Y, Table_Y3, 200, 1/13/2019
Schema_Z, Table_Z1, 700, 1/11/2019
Schema_Z, Table_Z3, 800, 1/12/2019
Schema_Z, Table_Z3, 900, 1/13/2019

X axis should represent day as well as Table name and Y axis should represent count.

I want a filter on top from where I can select which schema I want to see (Schema_X, Schema_Y, Schema_Z). Based on the schema I select from dropdown, the data should get updated appropriately in the line chart.

Can someone please tell me how do I achieve this?

Hi @kjkrupal
I’m not sure what you’re asking. Are you using Custom or Native question?
If you use the interface, then you just need to filter by Schema column. If Native question, then you need to use Field Filter.
It’s not possible to have a single-select dropdown.

Hi @flamber,

So I have around 17 schema names. Each schema has multiple tables specific to that schema. What I want to show is a line chart which shows how many records are inserted into tables over a period of time.

However there are 195 tables in total belonging to 17 schemas. Metabase line chart does not support more than 20 series so I cannot show all 195 tables data at once.

So I am planning to have a dropdown on top from where I can select SCHEMA_NAME. As soon as I select a schema_name, the line chart should update the table names and show appropriate data.

For example, lets say I am currently showing data for SCHEMA_X. SCHEMA_X has 4 tables X1, X2, X3 and X4. Now I want to view for SCHEMA_Y, so If I select SCHEMA_Y from dropdown, the line chart should remove data for X1, X2, X3, X4 and should display data for Y1, Y2 and Y3. As Y1 Y2 nad Y3 belong to SCHEMA_Y and X1, X2, X3, X4 belong to SCHEMA_X.

How do I do that?

Hi @flamber,

I will simplify this for you. Let’s take a scenario.

I have a table which contains data of number of children born everyday in different cities in different countries.

The table looks like this:

Country    City    Birth_count    Date
India    Mumbai    100       1/11/2019
India    Mumbai    120       2/11/2019
India    Mumbai    140       3/11/2019
India    Mumbai    160       4/11/2019
India    Delhi     101       1/11/2019
India    Delhi     121       2/11/2019
India    Delhi     131       3/11/2019
India    Delhi     141       4/11/2019
USA      NYC       100       1/11/2019
USA      NYC       125       2/11/2019
USA      NYC       135       3/11/2019
USA      NYC       145       4/11/2019
USA      Seattle   201       1/11/2019
USA      Seattle   220       2/11/2019
USA      Seattle   240       3/11/2019
USA      Seattle   250       4/11/2019

On the X axis I want the 4 dates (1/11/2019 to 4/11/2019) as time series. On the Y axis I want Birth Count. I want a drop down which country data I am viewing. If I select India, It should display data for Mumbai and Delhi. If I select USA, it should display data for NYC and Seattle.

@kjkrupal
I’m getting a little confused by your use of “schema”.
For the example table with Country, City, Count and Date - that’s definitely possible - both in QB and Native questions.
You would just return City, Count and Date - and then filter by Country. If you use Native question, then you would use Field Filter like this and reference the Country column in the sidebar:
SELECT City, Count, Date FROM table WHERE {{Country_filter}}

It’s currently not possible to change database schema with a filter variable:
https://github.com/metabase/metabase/issues/4639 - upvote by clicking :+1: on the first post
There’s work currently being done to expand the series limit from 20 to 100:
https://github.com/metabase/metabase/issues/3656 - upvote by clicking :+1: on the first post

Hi @flamber,

However, there are some Countries which may have more than 20 cities. Like if India has 10 cities, it can definitely show the data. However, If USA has more than 20 cities, I will not be able to visualize it. Is there any workaround for that?

@kjkrupal Until the chart limit is lifted to 100 series, then you would have to split countries with more than 20 cities. You could do that by adding a region or state.
Or adding another Number filter, which you’ll use to offset the query ... LIMIT 20 [[OFFSET {{number_filter}}]], so it would only show the first 20 cities and then you can input “20” in the filter and it would offset the results.