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.

[stevedark]

I've just found this post when looking for a solution I posted a while ago. I think the same solution applies, and you can find it here:
https://community.qlik.com/t5/New-to-Qlik-Analytics/Count-where-creation-date-is-before-X-in-combo-c...

Hope it helps someone.

Cheers,
Steve