Hi! I have a table in which I would like to calculate the difference between Weeks/Days/Hours/Minutes in the "Average Time In Backlog" and the "Average Time Created To Backlog" columns, and I was wondering if that is possible using Table Transformer/SQL. DateNumber Of Issues Average Time Created To Backlog Average Time In Backlog01 Jan 2022 6120w 3d 7h 33m48w 5d 5h 7m01 Feb 2022 6820w 6d 9h 1m43w 4d 25m01 Mar 2022 7723w 2d 12h 15m38w 6d 17h 28m This is my query, and I've tried varying Formatting options, but I can't seem to get it to work. SELECT T1.'Date', T1.'Average Time In Backlog', T1.'Average Time Created To Backlog', (T1.'Average Time Created To Backlog' - T1.'Average Time In Backlog') AS 'Difference' FROM T1 Any help would be greatly appreciated! :) Thank you so much! Best, Holly

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Calculating between Weeks and Days Table Transformer

Holly Louise Perry

Hi!

I have a table in which I would like to calculate the difference between Weeks/Days/Hours/Minutes in the "Average Time In Backlog" and the "Average Time Created To Backlog" columns, and I was wondering if that is possible using Table Transformer/SQL.

Date	Number Of Issues	Average Time Created To Backlog	Average Time In Backlog
01 Jan 2022	61	20w 3d 7h 33m	48w 5d 5h 7m
01 Feb 2022	68	20w 6d 9h 1m	43w 4d 25m
01 Mar 2022	77	23w 2d 12h 15m	38w 6d 17h 28m

This is my query, and I've tried varying Formatting options, but I can't seem to get it to work.

SELECT T1.'Date', T1.'Average Time In Backlog', T1.'Average Time Created To Backlog',
(T1.'Average Time Created To Backlog' - T1.'Average Time In Backlog') AS 'Difference'
FROM T1

Any help would be greatly appreciated! Thank you so much!
Best, Holly

Accepted answers

Natalie Paramonova _Stiltsoft_

Hi @Holly Louise Perry ,

Please add the "FORMATWORKLOG" function before counting the time difference:

SELECT FORMATWORKLOG(T1.'Time 2' - T1.'Time 1') AS 'Difference'
FROM T1

Besides you may go to the Table Transformer macro settings -> Settings tab -> Set worklog settings menu and check the parameters according to your case.

All comments

Natalie Paramonova _Stiltsoft_

Hi @Holly Louise Perry ,

Please add the "FORMATWORKLOG" function before counting the time difference:

SELECT FORMATWORKLOG(T1.'Time 2' - T1.'Time 1') AS 'Difference'
FROM T1

Besides you may go to the Table Transformer macro settings -> Settings tab -> Set worklog settings menu and check the parameters according to your case.

Holly Louise Perry

This is great! Thank you! Do you happen to know if there's a way to calculate a percentage of change between time (ie increase in time or decrease?)

I've done this with regular numbers in the past, but not with time. Here's the code I used for when I've done it with numbers (TT1 & TT2 were numbers).

SELECT TT1.*, TT1.'Date',
TT2.'Week' AS 'Previous week',
((TT1.'Overall Bug Badness Score'-TT2.'Overall Bug Badness Score')/TT2.'Overall Bug Badness Score')*100 AS 'Weekly Change'
FROM T1 AS TT1
LEFT JOIN T1 AS TT2 ON TT1.'Week' = (TT2.'Week' + 1)

Katerina Rudkovskaya _Stiltsoft_

Hi @Holly Louise Perry,

To count percent for the case, try to remove the "FORMATWORKLOG" function and create a required formula as you would do for regular numbers. It may look like this, for example:

SELECT *,
(T1.'Time 2' - T1.'Time 1')/T1.'Time 2'*100 AS 'Percent'
FROM T1

Then you may go to the Settings tab and choose how many decimal places you want to show.

Holly Louise Perry

Thank you so much! I was concerned that time wouldn't do it. This worked perfectly! Thank you both!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

confluence

server

confluence-server

sql

filter

table

table-filter

table-filter-and-charts

table-transformer