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_ · Accepted solution]

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.

[Holly Perry]

This is incredible and works perfectly - thank you so so much! :) You are the best!

[Srinatha Tondihal]

Hi @Holly Perry ,

Welcome to Atlassian community. 

If you are using the table transformer then you should be able to use the formulas as per below doc

• Custom transformation - Using formulas 

I could not try that but should be achievable i belive. 

Have a good day.!

Thanks,

Srinath T