I am building a look that tracks customer support tickets. Each ticket has three variables of interest: Created Date, Solved Date, and Points.
I am looking to create a line graph with 2 lines - Points vs Created Date and Points vs Solved Date.
It is easy to create these separately, but I can’t figure out how to combine them into one as it would need separate X axes. Despite both dimensions are dates, there doesn’t seem to be a way to have the X axis be a simple date, and then plot the two graphs on it based on the relevant dimension.
Anyone have any thoughts?
2 Separate X Axes - Dates
Hi Phil,
With regard to your query, I think that it would be extremely difficult to get the output you want from your current approach - the best way to get the result you want is to have one date and two measures, rather than two dates with one measure.
One approach to doing this would be to create a derived table where you bring in the ‘created date’ and ‘solved date’ under a single date with a ‘union all’, label them with a category, and use that to pivot the measure. For example, your PDT might look something like this:
view: pdt_example {
derived_table: {
sql:
SELECT
public.ufo_data.date_posted as date,
'Started' AS category,
COUNT(*) as tickets
FROM ufo_data
GROUP BY 1, 2
UNION ALL
SELECT
public.ufo_data.sighting_date as date,
'Ended' AS category,
COUNT(*) as tickets
FROM ufo_data
GROUP BY 1, 2
;;
}
dimension: date {
description: "The date"
type: date
sql: ${TABLE}.date ;;
}
dimension: category {
description: "Your category"
type: string
sql: ${TABLE}.category ;;
}
measure: tickets {
description: "Use this for counting lifetime orders across many users"
type: sum
sql: ${TABLE}.tickets ;;
}
}
This would give you the fields that you see in the attached image, allowing you to pivot on category to get two series which you can graph as lines.

I hope that helps!
Regards,
Gavin.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.