topic Count open and closed tickets in QlikView

Count open and closed tickets

Wed, 17 Feb 2010 23:07:26 GMT

I need a nudge in the right direction.

I've got an ticket number, an opened date, and a closed date on a table. What I want is to create a chart with the count of tickets that were opened and a second expression on the same chart that counts the number of tickets that were closed in a given month.

For whatever reason, I can't get my head around this.

Count open and closed tickets

johnw — Wed, 17 Feb 2010 23:30:56 GMT

So if you have this data:

Ticket Opened Closed other fields
1 Jan 7, 2009 Jan 9, 2009 blah blah blah
2 Jan 21, 2009 Feb 17, 2009 something
3 Feb 10, 2009 Mar 20, 2009 something else

You want to see this chart?

Opened Closed
Jan 2009 2 1
Feb 2009 1 1
Mar 2009 0 1

And if you then select Feb 2009, you want to see this?

Opened Closed
Feb 2009 1 1

Since you have two different dates, neither is the one you want for the chart. So one option is creating a date island, then matching it up in the chart with an if:

dimension = MonthIsland
Opened expression = count(distinct if(monthstart(Opened)=MonthIsland,Ticket))
Closed expression = count(distinct if(monthstart(Closed)=MonthIsland,Ticket))

It'll have all the usual downsides of a date island - slow on large data sets, selections behaving strangely, and so on.

Another option, if your data and other charts allow, is to change your data model to only have a single date field:

Ticket other fields
1 blah blah blah
2 something
3 something else

Ticket DateType Date
1 Opened Jan 7, 2009
1 Closed Jan 9, 2009
2 Opened Jan 21, 2009
2 Closed Feb 17, 2009
3 Opened Feb 10, 2009
3 Closed Mar 20, 2009

Date Month
Jan 7, 2009 Jan 2009
Jan 9, 2009 Jan 2009
Jan 21, 2009 Jan 2009
Feb 10, 2009 Feb 2009
Feb 17, 2009 Feb 2009
Mar 20, 2009 Mar 2009

Then you just make a pivot table:

dimension 1 = Month
dimension 2 = DateType
expression = count(distinct Ticket)

Count open and closed tickets

rwunderlich — Thu, 18 Feb 2010 01:24:47 GMT

I would create a table with a row for each Open and Close activity. Like this:

Activity:
LOAD
ID,
OpenDate as Date,
1 as OpenCount
RESIDENT Tickets;

CONCATENATE (Activity) LOAD
ID,
CloseDate as Date,
1 as CloseCount
RESIDENT Tickets;

Example attached.

-Rob

Count open and closed tickets

Thu, 18 Feb 2010 15:54:18 GMT

Thanks John. That got me back on track.

Count open and closed tickets

Thu, 18 Feb 2010 15:56:48 GMT

Rob, that's exactly what I did. Thanks for the reply. it seems so simple but for whatever reason yesterday, I couldn't get my brain to work right.

Stupid brain. 🙂

Count open and closed tickets

Tue, 23 Mar 2010 09:37:35 GMT

Hello John,

This sounds like something i would like to do, alternative number 2, could you give an example on how you would achieve this?

-Fredrik

Count open and closed tickets

johnw — Tue, 23 Mar 2010 19:04:30 GMT


heidenberg wrote:This sounds like something i would like to do, alternative number 2, could you give an example on how you would achieve this?

That WAS an example of how to achieve it. But if you need a QVW, here's what you get if you turn the tables I mentioned into inline loads, and create the pivot table as described.