Chart multiple series (field filter)?

I have a question that uses a field filter that has two possible results, location a and location b. My question charts out daily orders and allows the user to toggle between the two locations or see both locations combined. What is the best way to chart out location a and location b data and show it at the same time as different series? Is it possible to create a toggle for the user to switch between? Thank you

Hi @madkap
I might be misunderstanding, but couldn’t you just group by location also, so it would be it’s own series in the chart?
Can you show the query or a screenshot of the chart?

Here is very simple query that I’m using to output the line chart. I currently have start and end variables set up as single date filters where the user can define these values in the dashboard view. {{category}} is a field filter variable mapped to a location field in my table. There are currently 2 possible locations and the user can see the data in the dashboard with no filter (both locations combined) or with one of the 2 locations filtered.

I am wondering if there is some sort of toggle for the user so they can see line charts for both locations graphed out separately on the same graph?

Thank you

select date, sum(sales) as Sales from line_items
    where true
    [[and completed_at >= {{start}}]] 
    [[and completed_at <= {{end}}]]
    [[and {{category}}]]
group by date

@madkap
Yes, by selecting and grouping the column of what {{category}} is referencing.

select date, location, sum(sales) as Sales from line_items
    where true
    [[and completed_at >= {{start}}]] 
    [[and completed_at <= {{end}}]]
    [[and {{category}}]]
group by date, location

@flamber

Thanks this works. I added a series breakout and the default view is now with two separate series broken out with the user able to select either one. Is there a way for the user to toggle between this view and a view with data combined into one single series?

@madkap
Yes, but it’s a bit of a hack with so-called Complex Default Value.
You create another filter (Number), which you call “Show separated=1” (or whatever makes sense), so when it’s filled out as 1, then you separate the charts otherwise they’re combined.
I’m not sure which database you’re querying, so you might have to change # to -- or //, whatever you use for commenting in your database language.

select date
[[, location # {{separated}}]]
, sum(sales) as Sales from line_items
    where true
    [[and completed_at >= {{start}}]] 
    [[and completed_at <= {{end}}]]
    [[and {{category}}]]
group by date [[, location # {{separated}}]] 
1 Like

Dude you’re awesome, thanks

I have the same problem but group by doesn't work in my case. I'm writing the query for postgres.

@armanpanco How to show values of several variables of a field variable at the same plot?