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

[Anonymous]

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

[dwforest]

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.

[Anonymous]

Hi David,

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

[Anonymous]

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]

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";