You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I'm trying to create a table in confluence that estimates (wildly :)) the amount of effort left in a sprint.
I've used the JIRA filter maco to pull in the JIRAs in the current sprint, their status and their story points.
I've wrapped the filter macro in Table Transformer, but I'm having problems with a case statement based on the JIRA status field (which is being displayed as a picture/icon not text.)
Is there a way? I found a similar question about the Issue Type / T field, but that doesn't resolve my issue...
COUNT(T1.'Status') AS 'Issues',
SUM(T1.'Story Points') AS 'Story Points',
WHEN T1.'Status' = 'Closed' THEN 0
--more cases when I get this one to work...
ELSE 1 END AS Factor
--new column to add for Factor * SUM(T1.Story Points)
GROUP BY T1.'Status'
My 'Factor' always comes out as 1.
...I've tried lots of variations on the 'WHEN', using CONVERT or CAST, LIKE and % instead of =... but I'm out of ideas :)
Has anyone overcome this, or is it just not possible...
Hi @Niall Poole ,
Seems the issue is much easier: please check your formatting.
WHEN T1.'Status' = 'Closed' THEN 0
WHEN T1.'Status' = "Closed" THEN 0
By 'Column_name' we refer to columns and by "String" we refer to strings (cell contents).
The 'Factor' is not in the quotes - it is a column name as well.
Besides you may leave the THEN 0 and ELSE 1 without the quotes as they are numbers and not text.
What concerns the formatting, the Table Transformer applies its own while calculating data, sometimes it can be inconsistent if the source table has another type of text alignment. You may use the FORMATWIKI function (even not in this Table Transformer macro but in a separate one on top of this) and set the text alignment that you prefer.
Please check this part of our documentation: https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Changingalignment
I've done a couple of experiments with FORMATWIKI, so hoping I can work that out later...
It doesn't like Factor in double quotes (displays a syntax error) and in single quotes I get the same result - i.e. always 1 (with or without quotes around the 0 and 1).
Seems the initial structure is not correct:
COUNT, SUM and GROUP BY 'Status' count smth and group it by statuses (so you get as many rows in the result table as you have different statuses).
But the CASE WHEN part simply takes your table row by row and places specific numbers considering the status.
So, everything is mixed-up.
Maybe you need smth like this:
COUNT('Status') AS 'Issues',
SUM('Story points') AS 'Story points',
SUM('Factor') AS 'Factor'
CASE WHEN 'Status' IS "Closed"
GROUP BY 'Status'
Here we have the internal SELECT where we show all the columns and introduce the new 'Factor' column based on the CASE WHEN condition. Here we can refer to our table as T1.
And the external SELECT takes this new table with the additional 'Factor' column and do the aggregation. Here you can't write T1.'Column name', only the 'Column name' will do - now we work not with the original T1 table.
Here SUM('Factor') AS 'Factor' serves just for an example that firstly you need to set your values based on the condition row by row and only then group these values in the external SELECT.
You may clarify what you need to to do with the 'Factor' column and I'll try to help.