Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

I need to insert a formula within a Confluence table to return a value based on two dates

I am new to Confluence.  I want to use Confluence to track goals - completion date vs. target date.  If the completion date is within five days, I want to indicate that it is "On Time" in green; if it's more than 5 days late, but less than 10 days, I want to indicate that it is "Late" in yellow; and if it's more than 10 days, I want to indicate that it is "Very Late" in red.  

Can this be done and can you help me with the formula or direct me to the information that I need to figure this out on my own?

1 answer

Hi Pam,

You can do this with the help of the Table Transformer macro (one of the macros bundled in the Table Filter and Charts app). The macro allows performing complex calculations using SQL queries.

Here is an example.

Place two tables in the macro body. The first table is your source table with dates columns. The second one is a table with colored statuses.

2019-07-19_10h39_15.png

Edit the macro and switch to the SQL query tab. Use the following query:

SELECT *, CASE WHEN (('Completion date' - 'Target date') / "24h") < 5 THEN "ON TIME" WHEN (('Completion date' - 'Target date') / "24h") > 5 AND (('Completion date' - 'Target date') / "24h") <= 10 THEN "LATE" ELSE "VERY LATE" END AS 'Rating' FROM T1

2019-07-19_10h39_50.png

Set the correct date format in the Settings tab.

2019-07-19_11h41_12.png

The result is as follows.

2019-07-19_10h38_25.png

You can use a manually created table, the Jira Issues or other native Confluence macros outputting tables as a source table.

@Natalie Paramonova _StiltSoft_ 

i have managed to create table for similar requirement, but how color of the text changes in Rating column based on the value ?

 

can you send details on this please ?

- Gireesh

Hi @Gireesha Jeera ,

You need to have two tables wrapped in the Table Transformer macro:

  1. The table with date columns
  2. The small table with the required ratings

The second table is created with the help of the standard Status macro. It is often used to indicate the result or progress of different projects. When you create a status, you specify its name and the color of the lozenge.

Now if you go back to the SQL query, you may notice that we have a condition when to use this or that status based on the difference between the two dates from the first table and the name of the particular status:

SELECT *, CASE WHEN (('Completion date' - 'Target date') / "24h") < 5 THEN "ON TIMEWHEN (('Completion date' - 'Target date') / "24h") > 5 AND (('Completion date' - 'Target date') / "24h") <= 10 THEN "LATE" ELSE "VERY LATE" END AS 'Rating' FROM T1

As a result, we add an extra colored Ratings column to the first table based on the query.

Hope I've cleared this moment for you.

Like # people like this

@Natalie Paramonova _StiltSoft_ 

Thanks a lot, it really helped. appreciate quick help.

 

Regards,

Gireesh

morning @Katerina Kovriga _Stiltsoft_  ,

what if i want to compare with the current date with the Target date ? how to take current date for the same query above.

Regards,

Gireesh

Hi @Gireesha Jeera

Use the "today" function:

SELECT *, CASE WHEN ("today" - 'Target date') / "24h" < 5 THEN "ON TIME"
WHEN ("today" - 'Target date') / "24h" > 5 AND
("today" - 'Target date') / "24h" <= 10 THEN "LATE"
ELSE "VERY LATE" END AS 'Rating' FROM T1

Like # people like this

Thanks @Katerina Kovriga _Stiltsoft_ That worked perfectly. thank you once again.

Suggest an answer

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

Get to know the Confluence team!

Go “behind the screen” to meet some of the Confluence Cloud team. In this video series, we tackle some of the hard-hitting questions you never knew you wanted the answer to!  Meet some of the ...

236 views 0 10
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