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 .. 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 ..
@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
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 ..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.