Trouble creating line visualization


#1

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.


#2

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


#3

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;