Line chart plotting two lines based on different date fields

[Anonymous]

Good Afternoon,

I am new to Qlik Sense and trying to replicate line chart I create in Excel. Basically it is for open issues how many issue tickets were opened in a particular month vs how many were closed in the same month. The structure would be like:

Ticket Number	Open Date	Closed Date
001	04/15/2018	05/08/2018
002	04/18/2018	04/22/2018
003	05/11/2018	06/15/2018
004	05/17/2018	05/29/2018

I can plot the count of open or closed and that works fine but I can' figure out how to plot the count of the tickets opened in a month and the count of the tickets closed in a month. It generally just plots over the same line twice.

I have been reading about chart expressions, set expressions, and chart functions, tried probably a dozen or so variations and the most change I get is the second line disappears or is posted as a single point. Am I going in the right direction? Can you do this with expressions? I have also read some forum posts that seem to suggest multiple tables for each date type but I not sure if that is the direction I have to go either.

Thanks for any direction you can give me.

[jwjackso · Accepted solution]

I think I would consider using the crosstable function (https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/crosstab...)

Something like crosstable(DateType,DT,1) in front of the load should change your table to this:

[Ticket Number],DateType,DT

001,'Open Date','04/14/2018'

001,'Closed Date','05/08/2018'

002,'Open Date','04/18/2018'

002,'Closed Date','04/22/2018'

003,'Open Date','05/11/2018'

003,'Closed Date','06/15/2018'

004,'Open Date','05/17/2018'

004,'Closed Date','05/29/2018'

If you have created a calendar table, now is the time (Creating A Master Calendar)

Now your chart will be a count of DateType by date.