Please help on calculating Aging

[ankitgupta]

Hi , I am trying to solve a problem and need help. 

I have an excel file (attached) which is an output from a SQL table, basically the data shows # of issues reported. The way data captured in the backend is like this...

1. Right from the very first time when the trouble ticket got created (Status New) an entry in the DB got created with a unique request ID.

2. Every time the status of the ticket got changed a new line item in the database got created.

What I am trying to solve is this.

1. I am trying to generate a column called as Aging, where I want the difference between the dates when the ticket first created (Min date) and the what's the latest status of the particular date (Max date) of the request. The Aging Value only required in front of the Max date of the the Request ID 

2. Also, I want to capture on a particular day, how many New tickets created Vs. Completed

Appreciate if anyone can help me on this.

Thanks,

Ankit

[pedrodrfaria]

Hi @ankitgupta 

See below for the WF:

At the top we calculated the aging. At the bottom we checked per day how many were new and how many were created

[ankitgupta]

Thanks pedrodrfaria, but the problem I am trying to solve is bit different... let me try to explain.

Let say a New ticket opened with # 12345...

1. I got a ticket on 3/1/2021 - Status at this stage is New 
2. The status of the ticket changed on 3/2/2021 as Assigned 
3. The status of the ticket is changes on 3/3/2021 as In-Progress
4. Status again changed on 3/4/2021 as In UAT
5. Status again changed on 3/5/2021 as Completed

Now my DB have five rows in the backend. I want to calculate...

1. The aging of the ticket 12334, E,G Day one it landed and whatever the current status on the Max date. E.G. In the above scenario Min Date is 3/1/2021 and Max Date is 3/5/2021 so the aging is 5 Days. 

[pedrodrfaria · Accepted solution]

Hi @ankitgupta 

So this is what you are looking for?

Pedro.

[ankitgupta]

Thanks a bunch Pedrodrfaria, this is helpful.