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.
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:
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:
This is with a dumb example query: select 'Jan 18', 1000, 20 union select 'Feb 18', 1200, 30 union select 'Mar 18', 1400, 38;