if we have a complicated computation to do, it is very helpful to split it up into simpler pieces.
in TABLES, we might spread the calculation over several COLUMNS, so each column has a simple formula, and the last one combines the ‘intermediate’ results together to give the final computation.
an example; compute the final discounted payment amount using these column formulas.
each of these formulas goes into the corresponding COLUMN of the table…
// users discount percentage; column UserDiscPc
switchif(
Customer.Class<=4 or Customer.Category='A', 0.15,
Customer.Type<=8 or Customer.Category='B', 0.25,
Customer.Type<=10 or Customer.Category='C', 0.33,
0.0
)
// volume discount percentage column; VolDiscPc
if(Quantity>50,
Quantity*0.2,
Quantity*0.1
)
// total gross amount; column GrossAmount
Quantity*UnitPrice
//total discount amount to be deducted; column TotDiscAmount
GrossAmount*CustDiscPc + GrossAmount*VolDiscPc
// nett amount after deductions; column NettAmount
GrossAmount - TodDiscAmount
however, there is a way to combine these into a SINGLE formula but still break down the computation into smaller steps - by using the withname() function after each calculation
// users discount percentage; called UserDiscPc
switchif(
Customer.Class<=4 or Customer.Category='A', 0.15,
Customer.Type<=8 or Customer.Category='B', 0.25,
Customer.Type<=10 or Customer.Category='C', 0.33,
0.0
)
.withname( UserDiscPc,
// volume discount percentage called; VolDiscPc
if(Quantity>50,
Quantity*0.2,
Quantity*0.1
)
.withname( VolDiscPc,
// total gross amount; called GrossAmount
tonumber (Quantity*UnitPrice)
.withname( GrossAmount,
//total discount amount to be deducted; called TotDiscAmount
tonumber(GrossAmount*UserDiscPc + GrossAmount*VolDiscPc)
.withname( TotDiscAmount,
// nett amount after deductions; final result
GrossAmount - TotDiscAmount
)))) // close-out all the withnames
we have the same clarity but we dont need to create all those intermedery columns.
i have aligned the withname() functions to the right for clarity only.
max