Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I’m looking for a way to count the month number of a long list of projects (start dates over many years) so that I may compare month 1, month 2, etc of each project to each other. Does anyone have a method I can try?
Current Data:
Project # |
Project Start Date |
Project End Date |
ABC1 |
5/4/2021 |
10/10/2021 |
QWERTY2 |
1/1/2022 |
7/23/2022 |
XZY3 |
5/5/2023 |
8/1/2023 |
Desired Output:
Project # |
Calendar Month |
Month Number |
ABC1 |
May 2021 |
1 |
ABC1 |
June 2021 |
2 |
ABC1 |
July 2021 |
3 |
ABC1 |
Aug 2021 |
4 |
ABC1 |
Sept 2021 |
5 |
ABC1 |
Oct 2021 |
6 |
QWERTY2 |
Jan 2022 |
1 |
QWERTY2 |
Feb 2022 |
2 |
QWERTY2 |
March 2022 |
3 |
QWERTY2 |
Apr 2022 |
4 |
QWERTY2 |
May 2022 |
5 |
QWERTY2 |
Jun 2022 |
6 |
QWERTY2 |
Jul 2022 |
7 |
XZY3 |
May 2023 |
1 |
XZY3 |
Jun 2023 |
2 |
XZY3 |
Jul 2023 |
3 |
XZY3 |
Aug 2023 |
4 |
Hello, here I have what you need.
Try this code:
// First, we load the project data
Projects:
LOAD *,
Date#([Project Start Date], 'M/D/YYYY') as StartDate,
Date#([Project End Date], 'M/D/YYYY') as EndDate INLINE [
"Project #", "Project Start Date", "Project End Date"
ABC1, 5/04/2021, 10/10/2021
QWERTY2, 1/01/2022, 7/23/2022
XZY3, 5/05/2023, 8/01/2023
];
// Then, we generate the months for each project
ProjectMonths:
LOAD
[Project #],
Date(MonthStart(StartDate, IterNo() - 1), 'MMM-YY') as CalendarMonth,
IterNo() as MonthNumber
RESIDENT Projects
While MonthStart(StartDate, IterNo() - 1) <= EndDate;
Regarts.
Hello, here I have what you need.
Try this code:
// First, we load the project data
Projects:
LOAD *,
Date#([Project Start Date], 'M/D/YYYY') as StartDate,
Date#([Project End Date], 'M/D/YYYY') as EndDate INLINE [
"Project #", "Project Start Date", "Project End Date"
ABC1, 5/04/2021, 10/10/2021
QWERTY2, 1/01/2022, 7/23/2022
XZY3, 5/05/2023, 8/01/2023
];
// Then, we generate the months for each project
ProjectMonths:
LOAD
[Project #],
Date(MonthStart(StartDate, IterNo() - 1), 'MMM-YY') as CalendarMonth,
IterNo() as MonthNumber
RESIDENT Projects
While MonthStart(StartDate, IterNo() - 1) <= EndDate;
Regarts.
I thought this was the solution, however, I am now seeing an issue - CalendarMonth and MonthNumber are not associated to my original data, so I am unable to total the number of orders per month.
Is there a way to retain that information with this script?
Hello, what fields do they have and what tables, to create an example.
To create the original table "Current Data", I used a field called "Project Expenditure Date" to calculate both the "Project Start Date" and "Project End Date".