Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Display Logic

Ramu
Contributor
May 23, 2025

Dear All,

 

In the Confluence, Table Transformer, I'm trying to derive a logic to colour the output cells based on a condition   but not successful though:

Table:

Date Colour.jpg

Note:

((I'm using a CONDITION  ->

"FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy')

for deriving the date less than 11 weeks of Target End date i have for the TKTS)) ..

and the result of this condition which is a Date which if less than Today(CURDATE), then i want to color that cell bg as RED .. if not, no need to color 

 

the ask is -> if the result of this condition is less than CURDATE, then colour the bg of those Date cells as 'RED'..

 

in other words ... when the Date is Overdue(Past), then i want to colour the Background of that cell in 'RED' ..

 

Code:

SELECT

T1.'Key',

T1.'Summary',

T1.’Target end’,

FORMATWIKI("{cell:bgColor=red}", (FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy') < CURDATE), "{cell}") AS ‘Date'

FROM T1

 

also, have tried the following:

1.

FORMATWIKI

(
CASE
WHEN (FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy') < CURDATE) THEN '{cell:bgColor=red}'
END
) AS 'Date'

 

<This returns nothing .. no values at all in the Date col>

 

2.

FORMATWIKI ("{cell:bgColor=red}", (DATEADD('week', -11, T1.[Target end]) < CURDATE), "{cell}") AS 'Date'

 

3.

FORMATWIKI("{cell:bgColor=red}", WHEN FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy') < CURDATE, "{cell}") AS 'Date'

 

4.

IF FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy') < NOW () Then FORMATWIKI("{cell:bgColor=yellow}",'Date',"{cell}"),

 

tried few more combinations and finally reaching out to you seeking assistance ..

 

Please Help ..

 

1 answer

0 votes
sai rohit May 23, 2025

@Ramu  Try this,

SELECT
T1.[Key],
T1.[Summary],
T1.[Target end],
FORMATWIKI(
FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy'),
IF(DATEADD('week', -11, T1.[Target end]) < CURRENT_DATE, '{cell:bgColor=red}', '{cell}')
) AS '11 Weeks Before Target End'
FROM T1

Ramu
Contributor
May 23, 2025

Thanks Sai Rohit ..

There is no error however, dint color the Background of the Date cells where the Date is less than Target end..

 

even i have tried converting both the Date formats (the condition and the CURDATE or NOW) as follows:

IF (FORMATDATE(DATEADD('week', -11, T1.[Target end]), 'dd/MM/yyyy') < FORMATDATE(CURDATE(), 'dd/MM/yyyy'), '{cell:bgColor=red}', '{cell}')

<checked the settings for the Date format in Table Transformer and made sure it reflects dd/mm/yyyy as well>

 

no error but It is simply not returning the output at all ..

Ramu
Contributor
May 24, 2025

finally worked on my 32nd attempt .. ooophhh .. !!


heres' the code

CASE
WHEN FORMATDATE(DATEADD('week', -11, T1.'Target end'), 'dd/MM/yyyy') < FORMATDATE(CURDATE, 'dd/MM/yyyy')
THEN FORMATWIKI("{cell:bgColor=yellow}", FORMATDATE(DATEADD('week', -11, T1.'Target end'), 'dd/MM/yyyy'), "{cell}")
END AS 'Date'

Like Mirek likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events