How to create a timeline chart in confluence via using Chart from table+Table transformer

[Oujy_lala]

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;

[Aliaksei Mikhailau _Stiltsoft_]

Hello @Oujy_lala ,

Based on what I see, you use macros of our Table Filter, Charts & Spreadsheets for Confluence app.

Could you please show the result of Table Transformer macro? You can copy Table Transformer with everything inside, paste it somewhere else outside out of Chart from Table macro, and check the resulting table in the page view mode.

It could be also helpful if you show the settings of your Chart from Table macro.

Best regards,

Alexey 

[Oujy_lala]

Hi @Aliaksei Mikhailau _Stiltsoft_ ,

Thanks for your replied. Just had attached the result here, could you please help to check if it's the ' the result of Table Transformer macro' that you had mentioned above?

[Aliaksei Mikhailau _Stiltsoft_]

Hello @Oujy_lala,

Thank you for the provided information.

In your Chart from Table please try specifying the date format as My (just type it in). This should help.

[John Funk]

Hi @Oujy_lala  - Since no one has responded yet, I suggest you open a support ticket with Atlassian to get their feedback. 

https://support.atlassian.com/contact/#/

[Oujy_lala]

Hi @John Funk ，

Thank you.