Count open and closed tickets

[#1]

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.

[johnw · Accepted solution]

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)

[rwunderlich]

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

[#4]

Thanks John. That got me back on track.

[#5]

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. 🙂

[#6]

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

[johnw]

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.