How can I subtract two numbers in the same column?

Holly Perry

Hi! I would like to know the best way to automatically show the difference between two cells in the same column. It should be a percent change from week to week. I'm using table transformer and SQL, and was wondering if there was code I could write to make this happen. 

I was able to write SQL for the first column because I hardcoded a number into my formula:

SELECT T1.'Overall',
((T1.'Overall'-558785)/558785)*100 AS 'Percentage from Original, %'
FROM T1

but this is not effective for calculating the weekly percentage difference. Any help would be incredibly appreciated! See screen grab. 

Katerina Kovriga {Stiltsoft}

Hi @Holly Perry ,

It may be a little tricky to achieve with the Table Transformer macro, but here is the solution that our developers have come up with:

SELECT TT1.*,
TT2.'Week' AS 'Previous week',
((TT1.'Overall'-TT2.'Overall')/TT2.'Overall')*100 AS 'Percentage from Original, %'
FROM T1 AS TT1
LEFT JOIN T1 AS TT2 ON TT1.'Week' = (TT2.'Week' + 1)

We think that it will be easier to use the Table Spreadsheet macro - you'll be able to work with it like in Excel (use cells' formulas, etc.). It will be more user friendly to achieve the case.