Breakdown complex formulas using WithName() function

Showcase Tips and Hacks
1

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

6 Likes
2

I suppose you mean switchIf?

3

true.

not as bad as the time i typed a h instead of the w while doing a zoom demo!

and got a ShitIf( Customer.Type=Corporate )

3 Likes
4

ShitIf would be a function that could be used on many aspects of life! :slight_smile:

2 Likes
5

Ha, and I wanted to record a video on this :slight_smile:

Nothing comes without compromises though — you save up on interim columns but you also miss out on caching potential there. This formula will calculate in its all entirety whenever any of the dependencies change their value. So this should also be used with care.

There’s a certain style how I prefer to use WithName — I think I briefly touched on it in some of my recent videos (on formula tables I guess). Very often I would not open lots of nested withnames and close them all in the end, but ‘chain’ them so that I only have one WithName variable available at the moment. (it’s not always like that but that’s what I aim towards) I’m yet to talk about that.

1 Like