Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
timevansmu
New Contributor

Ticket Trend - How to Count New, Open and Closed Tickets

Hi All,

I am new to QLIK Sense and after searching the forums for the best way to report on the trend of New, Open and Closed service desk tickets I have reached the limit of my capabilities and am seeking some guidance and help.

Each record has a Date Opened and Date Closed field and I would like to trend the volume of tickets by New, Opened and Closed over periods of time.

The method I'm using is to create a related table and flag count, see below:

TicketTrend:

LOAD *,

MonthName(TrendDate) as TrendMonthName

WHERE TrendDate <= Today();

LOAD

  [Ticket No],

  Date([Date Opened]+iterno()-1, 'DD/MM/YYYY')  as TrendDate,

    If(iterno()=1, 1,0) as Flag_New,

    If([Date Opened]+iterno()-1 = [Date Closed],1,0) as Flag_Closed,

    If([Date Opened]+iterno()-1 < [Date Closed],1,0) as Flag_Open

Resident [SN Tickets]

WHILE [Date Opened]+iterno()-1 <= [Date Closed];

The problems I have are:

1. How do I manage NULL Closed Dates - If the Closed Date is NULL no record is added to the TicketTrend table

2. The operation to update Flag_Closed is never successful

I have attached a sample source data file and app so you can see where I am at.

Look forward to learning from this problem.

Thanks in advance.

Tim

4 Replies
dwforest
Valued Contributor

Re: Ticket Trend - How to Count New, Open and Closed Tickets

A solution would be to use IntervalMatch of the Open to Closed (or Today() if Closed is null) against a MasterCalendar generating a Flag_Closed, Flag_Open or a State Field with the value of Open or Closed per ticket per day.

This allows to total opened/closed stats for any day, week. month, quarter, year by looking at the last value for a ticket in the selected date range.

So for month end Jan 31, every ticket will have a value of opened or closed; yes this means tickets live forever and the database will grow, but Qlik works fairly quickly with this in my experience.

timevansmu
New Contributor

Re: Ticket Trend - How to Count New, Open and Closed Tickets

Hi David,

Thanks for the response, I will look at this option and let you know how I get on.

timevansmu
New Contributor

Re: Ticket Trend - How to Count New, Open and Closed Tickets

After a little more time I have solved my problems.

1. Added a test for NULL Date Closed.

2. The date comparison of Date Opened to Date Closed was not working because my dates were timestamped.  I resolved this by converting the dates to date only in the load script.

Revised Script:

TicketTrend:

LOAD *

WHERE %KeyDate <= Today();

LOAD

  [Ticket No],

  Date([Date Opened]+iterno()-1)  as %KeyDate,

    If(iterno()=1, 1,0) as Flag_New,

    If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 = [Date Closed],1,0),'0') as Flag_Closed,

    If(len(trim([Date Closed]))>0, If([Date Opened]+iterno()-1 <= [Date Closed],1,0),'1') as Flag_Open

Resident [SN Tickets]

WHILE [Date Opened]+iterno()-1 <= [Date Closed];

I have linked this table to my Master Calendar by the %KeyDate field.

Keitaru
New Contributor III

Re: Ticket Trend - How to Count New, Open and Closed Tickets

Hi I've got a question I'm facing a similar issue where by my Closed Dates flag are mostly showing 0

how did you solve this? able to provide the script of that portion as well as a sample of your qvf/qvw?

 

Adapted my script from yours

Main Loadscript where the are taking data from

LOAD
"Problem ID*+",

//Created Date
Date(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')) as "Problem Created Date",
Week(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')) as "Problem Created Week",
Month(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')) as "Problem Created Month",
Year(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')) as "Problem Created Year",
Date(MonthStart(Timestamp#("Problem Created Date",'DD/MM/YYY hh:mm:ss')),'MMM-YY') as "Problem Created MY",
Date(Timestamp#("Submit Date",'DD/MM/YYY hh:mm:ss')) as "Problem Submit Date",
Date(Timestamp#("Target Date",'DD/MM/YYY hh:mm:ss')) as "Problem Target Date",

// Closed Date
Date(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')) as "Problem Closed Date",
Week(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')) as "Problem Closed Week",
Month(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')) as "Problem Closed Month",
Year(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')) as "Problem Closed Year",
Date(MonthStart(Timestamp#("Closed Date",'DD/MM/YYY hh:mm:ss')),'MMM-YY') as "Problem Closed MY",

FROM Problem.qvd (qvd);

 

OutstandingTrend:

Load *,

MonthName(TrendDate) as TrendMonthName,
WeekName(TrendDate) as TrendWeekName

Where TrendDate <=Today();

Load
"Problem ID*+",

Date("Problem Created Date"+IterNo()-1,'DD/MM/YYY') as TrendDate,

if(IterNo()=1,1,0) as Flag_New,
 If(len(trim("Problem Closed Date"))>0,if("Problem Created Date"+IterNo()-1 = "Problem Closed Date",1,0),0) as Flag_Closed,
If(len(trim("Problem Closed Date"))>0,if("Problem Created Date"+IterNo()-1 < "Problem Closed Date",1,0),0) as Flag_Open,

Resident [Open Problem] WHILE [Problem Created Date]+iterno()-1 <= "Problem Closed Date";