Tracking of open and closed tickets

[psendra]

Good morning,

I have been iterating on this case for several hours now and I am not able to find a suitable solution.

I have a dataset with these fields:

Ticket ID
Ticket Description
Priority
Status
Owner
Date Created
Close Date

I want to get a table in Qlik Sense that allows me to calculate for a given period the number of tickets that were opened in that period, the number of tickets that were closed, the difference of tickets opened - tickets closed in that period and the total number of tickets that are still open accumulated in that period.

My main problem is that I don't know how to relate the dates to work with a single field that allows me to calculate all the expressions I need.

Could someone help me with this?

[MayilVahanan]

Hi

Try like below

Load *, Date(DateCreated + IterNo()-1) as Date While Date(DateCreated + IterNo()-1) <= Alt(DateClosed, Today());
Load * Inline
[
TID, DateCreated, DateClosed, Status
1, 10/11/2022, 29/11/2022, Closed
2, 14/11/2022, 19/11/2022, Closed
3, 28/11/2022, , Open
];

It will generate the dates between date created and closed.

In front end, you can use Date field for selection

Open Tickets: Count({<Status = {'Open'}>}DISTINCT TID)

Close Tickets: Count({<Status = {'Closed'}>}DISTINCT TID)

Tickets: Count(DISTINCT TID)

[psendra]

Thank you for your quick response.

In my case, in the status field I can have many values, not only open or closed. Also, I would like to calculate the tickets that were opened on a specific day, not the ones that are still open today.

I attach an example of my data in case it can help you.