Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

horatio166
New Contributor

Line chart plotting two lines based on different date fields

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 NumberOpen DateClosed Date
00104/15/201805/08/2018
00204/18/201804/22/2018
00305/11/201806/15/2018
00405/17/201805/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.

1 Solution

Accepted Solutions
jwjackso
Contributor III

Re: Line chart plotting two lines based on different date fields

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.

1 Reply
jwjackso
Contributor III

Re: Line chart plotting two lines based on different date fields

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.

Community Browser