Need help to get average ageing

[shahnawaz_khan]

Hi all

I m trying to pull the average ageing of all category, week wise of the month.

Output will look similar to below table:

Category	1st Week of the month	2nd Week of the month	3rd Week of the month	4th Week of the month	5th week of the month	Average ageing overall week
a	90	49	106	66		82
b	18	25	35	42		30
c	37	19	72	38	31	36
d	58	22		75	39	49
e	21	22		38	39	30
g		15	9	32		19
h			8			8
i			6			6
Grand Total	50	24	49	49	36	43

[binuacs]

@shahnawaz_khan Your attached workflow is not helping to understand what you are looking for? Can you elaborate on what exactly you're looking for based on your input data?

[Qiu]

@shahnawaz_khan 
I think you need to provide the definition of "aging" which is not shown in your sample data.

[caltang]

Also, assuming aging is the closure from creation date, why is there a time series element to the column names? The week from when? Need more details...

[shahnawaz_khan]

Yes ageing is from the closure from creation date, and the date format is dd-mm-yy  hh:mm;ss, and the week of the month stand for in which week it got closed. Eg: closed date is of 1st week of the month then its 1st week if closure date is of 4th week of the month then its 4th week.

[caltang · Accepted solution]

Like so?

Your grand total doesn't make sense to me. Is it a sum total or a total average of the field? Because if the latter, your grand total values need to be checked.

[caltang]

In addition, there are 52 weeks to a year, and if you are using closure week of the year, you need to factor that in. Unless you are saying, each month, the week recycles from 1 to 4 or 1 to 5. What about weeks cut in half? How do you consider a week starting in May but ends in June, will it be the 5th week of May or 1st week of June?

Needs more consideration into the what-ifs.. but if you are happy with the above, that's how you can do it.

[shahnawaz_khan]

Hi,

sample is below with Ageing : Ageing = Closure date - Created date

Sr no.	Category	Created date	Closure date	Week of the month	Ageing
1	a	01-01-2025	01-04-2025	1st Week	90
2	b	15-03-2025	02-04-2025	1st Week	18
3	c	07-02-2025	03-04-2025	1st Week	55
4	d	05-02-2025	04-04-2025	1st Week	58
5	e	15-03-2025	05-04-2025	1st Week	21
6	c	18-03-2025	06-04-2025	1st Week	19
7	g	23-03-2025	07-04-2025	2nd Week	15
8	a	18-02-2025	08-04-2025	2nd Week	49
9	b	15-03-2025	09-04-2025	2nd Week	25
10	c	18-03-2025	10-04-2025	2nd Week	23
11	d	20-03-2025	11-04-2025	2nd Week	22
12	e	21-03-2025	12-04-2025	2nd Week	22
13	c	30-03-2025	13-04-2025	2nd Week	14
14	g	05-04-2025	14-04-2025	3rd Week	9
15	h	07-04-2025	15-04-2025	3rd Week	8
16	i	10-04-2025	16-04-2025	3rd Week	6
17	a	02-01-2025	17-04-2025	3rd Week	105
18	a	01-01-2025	18-04-2025	3rd Week	107
19	b	15-03-2025	19-04-2025	3rd Week	35
20	c	07-02-2025	20-04-2025	3rd Week	72
21	d	05-02-2025	21-04-2025	4th Week	75
22	e	15-03-2025	22-04-2025	4th Week	38
23	c	18-03-2025	23-04-2025	4th Week	36
24	g	23-03-2025	24-04-2025	4th Week	32
25	a	18-02-2025	25-04-2025	4th Week	66
26	b	15-03-2025	26-04-2025	4th Week	42
27	c	18-03-2025	27-04-2025	4th Week	40
28	d	20-03-2025	28-04-2025	5th week	39
29	e	21-03-2025	29-04-2025	5th week	39
30	c	30-03-2025	30-04-2025	5th week	31
31	a	19-02-2025	25-04-2025	4th Week	65
32	a	02-01-2025	01-04-2025	1st Week	89

[Qiu]

@shahnawaz_khan 
You will provide the defition for "Week"?
I can not see consistency from your week number.

[caltang]

@shahnawaz_khan I believe my workflow solves your need no?