Ageing Analysis

[Maddy29]

Hello Mentors ,
In my attached sheet I am trying to do ageing analysis. Please help me to get result under mentioned bucket in the sheet.

ageing wise for Open cases
-> how many are under 0-30 days , 31-60 days , 61-90 days and >90 days.
ageing wise for Closed cases
-  how long it took to close the incident.
->how many are under 0-30 days , 31-60 days , 61-90 days and >90 days ( took to close).
Average number of incident raised every month.
Is there any way to identify or mention or count the common description among all.
please help me to get the result.

[#2]

Hi @Maddy29 

I have attached my file with some formulas that should answer most of your questions.

[#3]

thank you sir, it really helped me. just for clarification on average number of incidents raised. - how will it work. In my long data set when I apply this formula it doesn't give me the right picture. if you help me to understand would be great sir. Thanks.

[#4]

Hi @Maddy29 

this was my formula example:

=COUNT(C2:C20)/((MONTH(MAX(C2:C20))-MONTH(MIN(C2:C20))+1))

The COUNT part should be clear, it just counts all entries in the range C2:C20

Next, I calculated the latest month of the dates in range C2:C20

MONTH(MAX(C2:C20))

Then, I calculated the earliest month of the dates in range C2:C20 

MONTH(MIN(C2:C20))

When you subtract the earliest month from the latest month and add 1, you should get the number of months in your range.

And last, I divided the count by the number of months.

This solution assumes that all dates are within the same calender year. It would not work properly if you have dates in different years. Maybe this is the reason why it does not work on your side.

A more robust solution would be this:

=COUNT(C2:C20)/DATEDIF(MIN(C2:C20),MAX(C2:C20),"M")

Try this one instead of my first proposal.

For information:

DATEDIF is an undocumented function that calculates date differences in years ("Y"), months ("M") or days ("D")

[#5]

thank you so much sir for your help and explanation would surely guide me.
appreciate that.

[#6]

Hi Maddy,

Hope you are doing good. I know this is not directly related to your question but I have a good visualization that can be used with your data if you are interested I can show you an example.

Thanks

[#7]

Hi Sir, Yes sure I would like to see and understand from other perspective as well. Best, Maddy