Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,296,363
Community Members
 
Community Events
165
Community Groups

How can I subtract two numbers in the same column?

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. Screen Shot 2022-04-20 at 12.32.09 PM.png

2 answers

1 accepted

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:

Thu 7-1.png

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)

Thu 7-2.png

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.

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

0 votes
Srinatha T Atlassian Team Apr 20, 2022

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

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

Have a good day.!

Thanks,

Srinath T

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

1,019 views 17 28
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you