Cumulative flow or Stacked Time Area type chart

[Green_ Desmond]

Hi,

I have some data that looks like this, in a table. Just a small sample!

ID	Created Date	State
344886	05/07/2024 15:07:19	New
344848	05/07/2024 09:30:11	New
344817	05/07/2024 07:11:24	In Development
344705	04/07/2024 15:15:26	In Development
344687	04/07/2024 10:40:13	New
344686	04/07/2024 10:37:24	In Analysis
344677	04/07/2024 08:52:48	Closed
344651	03/07/2024 14:01:47	In Development
344618	03/07/2024 09:09:58	New
344598	02/07/2024 19:51:48	New
344597	02/07/2024 19:48:17	New

I want to create a chart out of it, that would look a lot like this (ignore the data and dates in the following chart, it's just to make the example):

I can't see a way to cumulatively keep a count of items in a given state, across a given time frame. I'm getting some odd results where the Stacked Time Area chart is just totalling up numbers of issues per day, and also not recognising or showing the different State values (is this because it is text and not numeric)?

I'm certain there must be a way to do this, but I'm struggling too much with it right now!

Also, to note the date format from my export, including time, also seems to be causing a problem when I try and get a sensible chart from this. Any help on that too would be appreciated!

[Stiltsoft support · Accepted solution]

Hi @Green_ Desmond ,

We can suggest trying the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

You'll be able to count a number of different statuses per day firstly and then count them cumulatively via the Table Transformer macro.

Then you may wrap your modified table in the Chart from Table macro and choose the Stacked Time Area chart type to visualize your data. 

[Green_ Desmond]

I was already using the Chart from Table macro, and tried various stacked charts. I couldn’t get any of them to work as expected with the dataset I have described. 

Can you be clearer on the steps I  should follow, to do what I need?

For example, I don’t see an option for a cumulative count in a table transformer macro?

Are you suggesting I create a calculated column by using an inner query? Can you help me with this, based on my column names?

[Stiltsoft support]

Hi @Green_ Desmond ,

If you have questions regarding our app (that is Table Filter, Charts & Spreadsheets for Confluence), please refer to our support. 

The portal is confidential, so we'll be able to exchange page storages and see what you've got on the page currently, improve it, and send it back.

Besides, here we find related questions manually (by the app's and macros' names), so some questions can be missed.

For example, here in the original question you didn't use the app's name and didn't mention the Chart from Table macro at all. Only the original tag that is "data-center-for-confluence" gave us an idea that this question is for Confluence and not for Jira and that we can help here.

Also we have rather good documentation that can help to solve the most part of general questions.

For example, for your case you may use the following structure:

Then you may go to the documentation and calculate your statuses in the internal Table Transformer macro: https://docs.stiltsoft.com/tfac/dc-server/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Countingthedefinitevalueinacolumn

SELECT FORMATDATE(T1.'Created Date', "dd/mm/yy") AS 'Created Date',
SUM(IF(T1.'State' = "New", 1, 0)) AS 'New',
SUM(IF(T1.'State' = "In Analysis", 1, 0)) AS 'In Analysis',
SUM(IF(T1.'State' = "In Development", 1, 0)) AS 'In Development',
SUM(IF(T1.'State' = "Closed", 1, 0)) AS 'Closed'
FROM T1
GROUP by FORMATDATE(T1.'Created Date', "dd/mm/yy")

Then you may visit the documentation once more and count your numbers cumulatively in the external Table Transformer macro: https://docs.stiltsoft.com/tfac/dc-server/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Calculatingcumulative(running)totals

SELECT 'Created Date',
SUM (TT2.'New') AS 'New',
SUM (TT2.'In Analysis') AS 'In Analysis',
SUM (TT2.'In Development') AS 'In Development',
SUM (TT2.'Closed') AS 'Closed'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Created Date' >= TT2.'Created Date'
GROUP BY TT1.'Created Date'
ORDER BY TT1.'Created Date'

Now you may go to the Chart from Table macro and choose the Stacked Time Area chart type:

The X and Y scales' step is set to 1.

[Green_ Desmond]

That all works perfectly and was really well explained. I really appreciate this. 

I also learned that the order of adding the value columns to the chart is importantly too. Some of my earlier trials had the widest sandwich layer ( the Closed items) topmost, while it is actually better to order these as the bottom layer. 

[Danut M _StonikByte_]

Hi @Green_ Desmond,

If those items are actually issues from Jira, you could use the Cumulative Flow Diagram (CFD) macro offered by our Great Gadgets app. 

All you have to do is to configure the macro with a JQL that returns the Jira issues that you want to track and to generate its bands by issue statuses. 

See more example of this gadget (and the many others offered by the same app), in these articles:

• https://community.atlassian.com/t5/Jira-articles/Building-a-powerful-Kanban-dashboard-in-Jira-with-Great-Gadgets/ba-p/1664331
• https://community.atlassian.com/t5/App-Central/How-to-track-scaled-agile-SAFe-projects-in-Jira-with-Great/ba-p/1929694

You could start by installing the 1 month free trial in your Jira Data Center. Then import the Jira gadget as macro in Confluence Data Center. If you have any questions, feel free to contact us at support@stonikbyte.com.

Hope this helps. 

Danut.