How to create a timeline chart in confluence via using Chart from table+Table transformer
Hi team,
i have two tables(T1 and T2) which comes from different projects, the data of the tables are the tickets which created in JIRA. I had used Table tranformer to merge the data of different team, the SQL are as below. Both table has Lables, but a little different is that T1 labels only include the month info ( such Jan25, Feb24 etc ), via T2 is scrum base, it include both month info and sprint info, T2 labels format is 'MMMYY SPRINTxx:DD/MM/YY'(M=Month, Y=Year, D=Day), so i used SQL to get MMMYY from each table.
Now i want to create a timeline chart which can help the team to see the monthly trend, and it would base on 'Labels'(MMMYY). But when i tried to use Chart from table in confluence to create a time line chart, and found that cuold not work and received error below. But if i changed to column chart, it works. Have no idea why Lables(MMMYY) could not work , can someone please help to check how to fix this problem, how can i create a timeline chart ? Many thanks.
SELECT
T1.'Labels',
T1.'AAA'+T1.'AAA_PRJ' as 'AAA',
T1.'BBB'+T1.'BBB_PRJ' as 'BBB',
T1.'CCC'+T1.'CCC_PRJ' as 'CCC',
T1.'DDD'+T1.'DDD_PRJ' as 'DDD'
FROM (
SELECT
T1.'Labels',
T1.'Count AAA' AS 'AAA',
T1.'Count BBB' AS 'BBB',
T1.'Count CCC' AS 'CCC',
T1.'Count DDD' AS 'DDD',
SUM(T2.'Count AAA') as 'AAA_PRJ',
SUM(T2.'Count BBB') as 'BBB_PRJ',
SUM(T2.'Count CCC') as 'CCC_PRJ',
SUM(T2.'Count DDD') as 'DDD_PRJ'
FROM T1
LEFT OUTER JOIN T2
ON T1.'Labels' = SUBSTR(T2.'Labels', 1, 5)
where T1.'Labels'!="Total"
GROUP BY T1.'Labels',T1.'Count AAA',T1.'Count BBB',T1.'Count CCC',T1.'Count DDD'
) AS T1
ORDER BY
CAST(CONCAT('20', SUBSTR(T1.'Labels', 4, 2)) AS INTEGER),
CASE UPPER(SUBSTR(T1.'Labels', 1, 3))
WHEN "JAN" THEN 1 WHEN "FEB" THEN 2
WHEN "MAR" THEN 3 WHEN "APR" THEN 4
WHEN "MAY" THEN 5 WHEN "JUN" THEN 6
WHEN "JUL" THEN 7 WHEN "AUG" THEN 8
WHEN "SEP" THEN 9 WHEN "OCT" THEN 10
WHEN "NOV" THEN 11 WHEN "DEC" THEN 12
END ASC;
