Histogram on distinct data

[QlikSensor]

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?

[MarcoWedel]

please post some sample data and your expected result

[QlikSensor]

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

[MarcoWedel]

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;