Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to show a histogram how long tickets are opened. The problem is that the tickets are in the database several times since data is extracted every day to get a history. Now I want to show a histogram where each ticket is only counted once and I want to use those tickets where createdDate and closedDate are defined and if closedDate is not yet defined in a ticket I want to use today().
Is that somehow possible?
please post some sample data and your expected result
Ticket-ID CreatedDate ClosedDate ExtractionDate
123 01.03.24 - 01.03.24
123 01.03.24 - 05.03.24
456 02.03.24 - 01.03.24
456 02.03.24 04.03.24 05.03.24
I would expect to get a histrogram with 2 bars, one bar is for ticket 123 with 12 days (ClosedDate=today() minus CreatedDate=01.03.24) and another bar is for ticket 456 which is 3
Hi,
maybe one solution could be:
tabTicketDB:
Load [Ticket-ID],
Date#(CreatedDate,'DD.MM.YY') as CreatedDate,
Date#(ClosedDate,'DD.MM.YY') as ClosedDate,
Date#(ExtractionDate,'DD.MM.YY') as ExtractionDate
Inline [
Ticket-ID,CreatedDate,ClosedDate,ExtractionDate
123,01.03.24,,01.03.24
123,01.03.24,,05.03.24
234,01.03.24,,01.03.24
234,01.03.24,03.03.24,04.03.24
456,02.03.24,,01.03.24
456,02.03.24,04.03.24,05.03.24
];
tabTickets:
Load [Ticket-ID] as TicketID,
CreatedDate,
ClosedDate,
Alt(ClosedDate,Today())-CreatedDate as OpenDays
Resident tabTicketDB
Where not Exists(TicketID,[Ticket-ID])
Order By [Ticket-ID], ExtractionDate desc;
Drop Table tabTicketDB;