PiwerPivot - create ticket history

[nhoff]

I want to present open and close history of tickets with data coming from a IT service management tool. I have simplified the data structure for this purpose.

3 tables:

tblTickets
- fldTicketID
- fldTicketCreationDate
- fldTicketName
- fldStatus (1-7)
- fldPriority

tblDates
- fldDateID
- fldDate

tblTicketHistory
- fldTicketHistoryID
- fldTicketID
- fldStatusChangeDate
- fldStatusFrom
- fldStatusTo

When a ticket has reached status 6, it is closed. Status 1-5 is considered open. Status 7 is rejected

I would like to produce a pivottable (and chart) that shows the following information:

Date NumberOfNewTickets TotalNumberOfOpenTickets TotalNumberOfClosedTickets

 

[olivierhbh]

Hello Nhoff, that would be really helpful if you could supply an excel file with sample data in it.

Olivier.