36
I have records for Tasks in Sharepoint List with columns TaskName, StartDate, EndDate, Effort%
I want to generate collection with 1 record for each week with week number b/w start, end date and group, sum by Week number.
Example input and output.
Data source
| TaskName | StartDate | EndDate | Effort% |
| T1 | 8 Jan 2024 | 27 Jan 2024 | 45 |
| T2 | 1 Jan 2024 | 20 Jan 2024 | 20 |
Output Collection
| WeekNum | Effort% |
| 1 | 20 |
| 2 | 65 |
| 3 | 65 |
| 4 | 45 |
ClearCollect(taskEffort,
{TaskName:"P1", Week:2, Effort:50}
);
Clear(taskEffort);
ForAll(tasksAssigned As Tasks,
ForAll(Sequence(Trunc(DateDiff(Tasks.StartDate, Tasks.EndDate,TimeUnit.Days)/7), WeekNum(Tasks.StartDate)) As Weeks,
Patch(taskEffort, Defaults(taskEffort),
{
TaskName: Tasks.TaskName,
Week: Weeks.Value,
Effort: Tasks.Effort
}
)));
Share
Report
All responses (
Answers (
