Challenging LOGIC !! even couldn't find ans in ATLASSIAN community for 2 days now !! ..pls help

[Ramu]

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:

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 in the column 'Date' .. if not, no need to color 

the ask is -> if the result of this condition is less than CURDATE, then colour the background of those Date cells as '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

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}') AS 'Date'

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

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 ..

[Lucas Modzelewski _Lumo_]

Unfortunately, I’m not familiar with that app and haven’t worked with Data Center in a while.

1. You could try asking ChatGPT to help break it down - it might offer some ideas or context.

2. Have you tried reaching out to the app’s support team directly? They should be able to confirm whether this is possible and guide you through the steps if it is.

[Ramu]

ChatGPT or CoPilot couldnt help much in the initial Try outs ... but ChatGPT was useful in keep correcting my try outs from 28th one out of total 32 Try outs ..

from the last suggestion it gave, i applied one more logic(or altrnate way) and finally worked .. 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'

[Lucas Modzelewski _Lumo_]

Congrats @Ramu ! 🎉
Please share the solution that worked for you - it might save someone else a lot of frustration down the line.

[Ramu]

edited and typed in my last post :)