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
- Formula is selected. It writes
net_eur, the net amount in euros, as a plain copy ofamount_eurfor now. - Select Summarize.
grossreads 4093.5 andnetthe same, 4093.50.pricedcounts 91 amounts. - 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. - Read the preview. The first row, KB-10213, is a free staff hire, so its
net_eurstays NULL. The next, KB-10214, goes from 38.5 to 30.80. - Select Summarize.
netreads 3274.80, andpricedis 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
pricedwould count 93 and hide the two free rides. - Overwriting the original column. Keep
amount_eurand addnet_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.