Hello, I am currently setting up a page property report for an overview page that tracks individual jobs our team is working on. The columns are client, assignee, begin date + end date (as macros) etc.
Our team leader would now like to track the individual time any job lasts in a seperate column entitled "duration". In order to avoid manual calculation I would like to automate this task by calculating the delta between the begin date macro and the end date macro.
The result should show up as "5 days" or similar in a seperate column.
NB our jobs typically run uninterrupted for several days also over weekends, so there is no need to differentiate between business days and days off.
This use case can be resolved with the help of the Table Filter and Charts for Confluence app.
You can wrap your Pape Properties Report with the Table Transformer macro and enter the following SQL query:
SELECT *, DATEDIFF(day,'Begin date','End date')+ " days" as 'Duration' from T1
Please find our documentation to get more info how to use our app.
thank you so much for your answer, it worked out just fine. There are only two minor issues, hope you can help me with these as well:
1) The "Duration" now shows up in in decimal number days (14,9999999 days) instead of rounded integers (15 days) - can you tell me where to change this?
2) I also have Table Filter included to make it easier browse the files, my current page architecture looks like this:
Problem is that the report filter to help browse the page now appears BELOW the list, the team would prefer to have it on top of the list (specifically once we have several hundred entries it would be very clumsy to scroll all the way down to the list to start a filter search.
Maybe you can help there as well. Other than that, thanks for the quick reply here!
Let's try to replace that SQL query for the following:
SELECT *, ROUND(DATEDIFF(day,'Begin date','End date'))+ " days" as 'Duration' from T1
Regarding the second question, you need to wrap the Page Properties Report macro with Table Transformer at first and then add Table Filter.
Hey there, folks! For most of us, the past six months- yes, you read that right- have been a journey. More people than ever before have pivoted to working remotely, and navigating being on-scre...
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