Ideas/Advice on calculating a daily open case backlog.

[#1]

I have 150000 helpdesk tickets with open/close dates going back 4 years. What I am looking for are some ideas/advice on how to generate what the daily open case backlog has been.

I can sort of do this with Excel (creating a matrix of date/ticket number and adding up left open tickets by the day) but rapidly bump into the 2 million cell limit.

Thoughts appreciated.

David Rowe

[swuehl]

David,

There are probably some approaches / solutions to your request.

I think your problem might be similar to the problem here, instead of tickets with Open/Close date, the task there was to calculate attendance between StartTime and EndTime:

http://community.qlik.com/message/167598

Hope this helps,

Stefan

[#3]

Thanks Stefan,

Close - given me some idea's but no cigar. Their ID could be a 'ticket' number but I don't want to see the 'backlog' wrapped over the same time period.

I was thinking more along the lines of putting Date as a Column and then ticket numbers across putting a '1' in the ticket column each day/date the ticket was open. I would then be able to do a sum on the date giving me the total open tickets.

How would I go about auto generating ticket column numbers for existing tickets only?

Thanks

DR

[swuehl]

David,

I think I am missing something on your request, I think the referenced solution should possibly serve you to.

Maybe more like Rob or John suggested here?

http://community.qlik.com/message/7069

Could you maybe post a small example of how you would like your results to be displayed?

Regards,

Stefan

[#5]

The screen shot below is what I would like to see out of it with days/date on the X and a count on the Y. Let me see what I make of the 7069 post.

Thanks

DR

[swuehl · Accepted solution]

David,

John just posted another nice approach here:

http://community.qlik.com/thread/38841

[#7]

Thanks Stefan,

It got me where I needed to go but still trying to get my head around how INTERVALMATCH does it.

DR

[swuehl]

Have you looked at the samples in the manual / Help file? With the created synthentic key, it not so easy to see what's going on, but you could try to join the tables, maybe using (for the above example):

Match:

left join INTERVALMATCH (HOUR)

LOAD

TIMESTAMPIN

,TIMESTAMPOUT

RESIDENT Log

;