Trouble creating line visualization

Hi.

I want to create a line visualization from the following table:

“Subscribers” “Jan 18” “Feb 18” “Mar 18” “Apr 18”
“Subscribers” “1000” “1200” “1400” “1800”
“Complaints” “20” “30” “38” “42”

(Here’s a simple query just for this example:)

select 'Subscribers', 1000 as 'Jan 18', 1200 as 'Feb 18', 1400 as 'Mar 18', 1800 as 'Apr 18'
UNION ALL
select 'Complaints', 20, 30, 38, 42

There are 2 variables - ‘Subscribers’ and ‘Complaints’, and I want to plot both with the vertical axis being the numbers, and the horizontal axis being the months.

But I cannot work out how to do this in the ‘line’ visualization settings.

Any help gratefully appreciated, thanks.

Normalise your data first.
It needs to be in the format
SubscriberType, Date, Quantity

For example:
Select 'Subscriber' as SubType, 1-1-2018 as SubDate, 1000 as Quantity
UNION ALL
etc etc

Then create a question for subscribers and a question for complaints as separate questions. Add one to the dashboard, then add the second to the first.
Like the top chart on this dashboard:
https://mail.pursuittechnology.co.uk:8443/dashboard/1

It's actually 2 questions displayed as 1 by adding the 2nd question to the first:
image

You actually don't have to do this as separate questions. If you're able to just "rotate" your query so that the variables/metrics are each a column, and the time stamp is a column, you can plot it easily like this:

plotting-sql

This is with a dumb example query:
select 'Jan 18', 1000, 20 union select 'Feb 18', 1200, 30 union select 'Mar 18', 1400, 38;

1 Like