How could I reduce the code execution in a ForAll? I thought I could use 'in' but looks like I need to run the Weekday function twice and do a
> 1 AND < 7 method
I am trying to exclude weekends in building a collection of dates:
ForAll(
Sequence(10),
If(Weekday(DateAdd(
inpDay_1.SelectedDate,
Value
)) in [2,3,4,5,6],
Collect(
RequestDates,
{
Day: DateAdd(
inpDay_1.SelectedDate,
Value
)
}
))
but needs
If(
Weekday(DateAdd(inpDay_1.SelectedDate,Value)) > 1 AND
Weekday(DateAdd(inpDay_1.SelectedDate,Value)) < 7
)
which seems to unnecessarily double the calculations ?
Solved! Go to Solution.
Depending on how you write it, you can use formula-level variables (i.e. With). However, usually UpdateContext/Set/etc. are somewhat limited given the conflict possibilities. Since you're collecting (another way to track runs in a loop), this is a straight-forward formula.
It's not clear above if that's an Items property or an OnSelect to build a collection. Either way, I recommend the latter - just decide when to build the collection.
Then use this code, or something like it.
Clear(colDates);
// Reset Collection
With(
{
varDaysToAdd: 20, // Your business days to add
varDateStart: Today() // Your initial date
},
ForAll(
Sequence(varDaysToAdd) As Loop,
If(
IsEmpty(colDates),// Is Collection Empty?
Collect(
colDates,
// Initial Collection
{
tblRef: Loop.Value,
tblDate: varDateStart
}
),
With(
// Subsequent Collections
{
varLastDate: Last(
Sort(
colDates,
tblRef,
Ascending
)
).tblDate
},
Collect(
colDates,
{
tblRef: Loop.Value,
tblDate: DateAdd(
varLastDate,
Switch(
Weekday(varLastDate),
7,// Saturday
2,// Add 2 Days
6,// Friday
3,// Add 3 Days
1// Else Add 1 Day
),
Days
)
}
)
)
)
)
)
Depending on how you write it, you can use formula-level variables (i.e. With). However, usually UpdateContext/Set/etc. are somewhat limited given the conflict possibilities. Since you're collecting (another way to track runs in a loop), this is a straight-forward formula.
It's not clear above if that's an Items property or an OnSelect to build a collection. Either way, I recommend the latter - just decide when to build the collection.
Then use this code, or something like it.
Clear(colDates);
// Reset Collection
With(
{
varDaysToAdd: 20, // Your business days to add
varDateStart: Today() // Your initial date
},
ForAll(
Sequence(varDaysToAdd) As Loop,
If(
IsEmpty(colDates),// Is Collection Empty?
Collect(
colDates,
// Initial Collection
{
tblRef: Loop.Value,
tblDate: varDateStart
}
),
With(
// Subsequent Collections
{
varLastDate: Last(
Sort(
colDates,
tblRef,
Ascending
)
).tblDate
},
Collect(
colDates,
{
tblRef: Loop.Value,
tblDate: DateAdd(
varLastDate,
Switch(
Weekday(varLastDate),
7,// Saturday
2,// Add 2 Days
6,// Friday
3,// Add 3 Days
1// Else Add 1 Day
),
Days
)
}
)
)
)
)
)
User | Count |
---|---|
195 | |
123 | |
86 | |
48 | |
41 |
User | Count |
---|---|
281 | |
163 | |
138 | |
80 | |
76 |