Lesson 7 of 10 · Lab

Add a calculated column to a table

Formula adds a column calculated from each row's values and keeps the number of rows. Write the rule once and it applies to every row, including the ones you never look at.

Till prices include VAT. Marit reports her figures without it, so VAT has to come off every amount before anything is added up.

Where fictional Kramerica Bikes trades, a fifth of every till price is VAT, so net is the amount × 0.8. This live flow in Strata, Oplema's browser app for data prep, starts with the 93 finished hires your filter kept in the last lesson.

Two of the week's hires were free staff rides with no amount. What should their net be?

Empty, like their amount. Formula keeps NULL as NULL, which is right: no money came in, and a sum skips it.

Take VAT off every hire

  1. Formula is selected. It writes net_eur, the net amount in euros, as a plain copy of amount_eur for now.
  2. Select Summarize. gross reads 4093.5 and net the same, 4093.50. priced counts 91 amounts.
  3. Select Formula. Select the Expression field, select all its text with Ctrl+A or Cmd+A and type amount_eur * 0.8. Then select the Formula block. Its Output type, DECIMAL with 2 decimal places, keeps the money in exact cents when it's added up. If red text appears under Expression, a column name is mistyped.
  4. Read the preview. The first row, KB-10213, is a free staff hire, so its net_eur stays NULL. The next, KB-10214, goes from 38.5 to 30.80.
  5. Select Summarize. net reads 3274.80, and priced is still 91.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What you should see

net €3,274.80, which is the €4,093.50 gross × 0.8. hires stays 93, because a calculated column never adds or removes rows, and the two free hires, KB-10213 and KB-10254, stay NULL.

One rule, every row

Checking against gross × 0.8 is worth doing every time, because a formula that runs isn't necessarily right. Type amount_eur * 1.2 and every row changes without a warning.

Calculate on rows first and total afterward. Here both orders agree, because every hire has the same rate. They stop agreeing once a rule differs from row to row, such as a discount for some customers. Next, total the week by bike code.

See the Formula reference. For later: Calculated columns compares row-level formulas with aggregates.

Common formula mistakes

  • Dividing by 1.2. That suits VAT charged at a fifth on top of the net price. Here a fifth of the till price itself is VAT, which is × 0.8, the same as ÷ 1.25.
  • Filling NULL with 0 to make the column look complete. The total stays the same, but priced would count 93 and hide the two free rides.
  • Overwriting the original column. Keep amount_eur and add net_eur, so anyone can check one against the other.

Calculated columns in SQL and Excel

SELECT *, CAST(amount_eur * 0.8 AS DECIMAL(18, 2)) AS net_eur
FROM week;

SELECT sum(amount_eur * 0.8) AS net,
       sum(amount_eur) * 0.8 AS check_net
FROM week;

A calculated column in SQL is an expression in SELECT. In Excel, =F2*0.8 filled down a column does the same, but an empty F2 gives 0 rather than empty, one more reason to check the count as well as the sum.