Question

2 Separate X Axes - Dates

  • 9 July 2018
  • 1 reply
  • 763 views

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?


1 reply

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