Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;