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: Category1st Week of the month2nd Week of the month3rd Week of the month4th Week of the month5th week of the monthAverage ageing overall weeka904910666 82b18253542 30c371972383136d5822 753949e2122 383930g 15932 19h 8 8i 6 6Grand Total502449493643 Week wise average ageing.yxmd

Need help to get average ageing

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

Week wise average ageing.yxmd

Help

Accepted answers

caltang

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.

Week wise average ageing.yxmd

All comments

binu_acs

@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

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.

Week wise average ageing.yxmd

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.

2025-04-18 140221.png

caltang

@shahnawaz_khan I believe my workflow solves your need no?

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157