Feb 20 2022 04:45 AM
I picked up a banded commission problem from another forum and tried to apply routines I had written for FIFO allocation. It may have been a sledgehammer to crack an awkwardly shaped nut, but the Lambda function produced a list of commissions payable on each service without difficulty.
The OP was only interested in the total commission payable to the employee so I applied SUM to the result ... , and got zero! Using REDUCE to sum the commission worked.
"Fail"
= LET(
commission, Allocateλ(salesAmount,Bands, Rate),
SUM(commission))
"Success"
= LET(
commission, Allocateλ(salesAmount,Bands, Rate),
REDUCE(0,commission,Addλ))
Any ideas?
Feb 20 2022 05:01 AM
For what it is worth, these are the other named formulae
Accumulateλ = LAMBDA(arr, [init], [opening],
LET(
acc, SCAN(init, arr, Addλ),
open, IF(ISOMITTED(opening), 1, opening),
accumulated, IF(open, acc, acc - arr),
accumulated
)
);
Addλ = LAMBDA(x, y, (x + y));
Allocateλ = LAMBDA(outputArray, inputArray, [weight],
LET(
cumulativeOutput, Accumulateλ(outputArray, 0, {0, 1}),
cumulativeInput, Accumulateλ(inputArray, 0, {0, 1}),
m, ROWS(outputArray),
n, ROWS(inputArray),
allocationArray, MAKEARRAY(
m,
n,
LAMBDA(k, h,
LET(
u, {-1, 1} * INDEX(cumulativeOutput, k, {1, 2}),
v, {-1, 1} * INDEX(cumulativeInput, h, {1, 2}),
d, SUM(IF(u < v, u, v)),
q, IF(d > 0, d, 0),
w, IF(ISOMITTED(weight), 1, INDEX(weight, h)),
q * w
)
)
),
IF(ISOMITTED(weight), allocationArray, BYROW(allocationArray, Sumλ))
)
);
Diffλ = LAMBDA(s, [s0], [d],
MAKEARRAY(
ROWS(s),
COLUMNS(s),
LAMBDA(k, h,
LET(
a, INDEX(s, k, h),
b, IF(
d,
IF(
h > 1,
INDEX(s, k, h - 1),
IF(TYPE(s0) = 64, INDEX(s0, k), s0)
),
IF(
k > 1,
INDEX(s, k - 1, h),
IF(TYPE(s0) = 64, INDEX(s0, h), s0)
)
),
a - b
)
)
)
);
Expandλ = LAMBDA(rowϑ,
LET(
n₁, ROWS(INDEX(rowϑ, 1)()),
n₂, COLUMNS(rowϑ),
result, MAKEARRAY(
n₁,
n₂,
LAMBDA(i₁, i₂, INDEX(INDEX(rowϑ, 1, i₂)(), i₁))
),
result
)
);
Sumλ = LAMBDA(x, SUM(x));
ThunkByColλ = LAMBDA(array, BYCOL(array, Thunkλ));
Thunkλ = LAMBDA(x, LAMBDA(x));
Feb 20 2022 08:51 AM
SolutionThis one works
= LET(
commission, Allocateλ(+salesAmount,Bands, Rate),
SUM(commission))
The difference is in +salesAmount . Auto-lifting not always works.
Feb 20 2022 11:47 AM
Thank you for that suggestion. The use of REDUCE did appear as overkill and was difficult to justify. The '+' is far neater and it appears to work used preceding any of the 3 parameters or even outside the Allocateλ function.
= SUM(+Allocateλ(salesAmount,Bands,Rate))
Now I just have to hope that someone contributes a more standard approach on the Chandoo forum, otherwise I may feel obliged to actually assist!
Feb 20 2022 08:51 AM
SolutionThis one works
= LET(
commission, Allocateλ(+salesAmount,Bands, Rate),
SUM(commission))
The difference is in +salesAmount . Auto-lifting not always works.