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 |
Solved! Go to Solution.
@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?
@shahnawaz_khan
I think you need to provide the definition of "aging" which is not shown in your sample data.
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...
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.
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.
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 |
@shahnawaz_khan
You will provide the defition for "Week"?
I can not see consistency from your week number.
@shahnawaz_khan I believe my workflow solves your need no?