Open tickets by periods

[micheledenardi]

Dear all, i have a dataset like this and i would like to create a line chart where, per each day, i can see the amount of open tickets on that day, so:

01/01/2023: 1 open ticket (ticket 1)

02/01/2023: 1 open ticket (ticket 1)

03/01/2023: 2 open tickets (ticket 1, 3)

04/01/2023: 2 open tickets (ticket 1, 3)

05/01/2023: 3 open tickets (ticket 3, 4, 5)

06/01/2023: 3 open tickets (ticket 3,4, 5)

07/01/2023: 3 open tickets (ticket 3, 4)

Ticket ID	Creation Date	Closure Date
1	01/01/2023	05/01/2023
2	01/01/2023	01/01/2023
3	03/01/2023	None (still open)
4	05/01/2023	None (still open)
5	05/01/2023	07/01/2023

How to do this? Thanks

[PedroNetto · Accepted solution]

Table1:
Load * inline [
Ticket ID, Creation Date, Closure Date
1, 01/01/2023, 05/01/2023
2, 01/01/2023, 01/01/2023
3, 03/01/2023,
4, 05/01/2023,
5, 05/01/2023, 07/01/2023
];

NoConcatenate

Table2:
LOAD [Ticket ID],
[Creation Date],
if(len(Trim([Closure Date])) = 0, date(Today()), [Closure Date]) as [Closure Date]
Resident Table1;

Table:

LOAD [Ticket ID],
Date([Creation Date] + IterNo() - 1) as Date
Resident Table2
While [Creation Date] + IterNo() <= [Closure Date];

DROP Table Table1;

[MarcoWedel]

https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944