Hi, I have some data that looks like this, in a table. Just a small sample! IDCreated DateState34488605/07/2024 15:07:19New34484805/07/2024 09:30:11New34481705/07/2024 07:11:24In Development34470504/07/2024 15:15:26In Development34468704/07/2024 10:40:13New34468604/07/2024 10:37:24In Analysis34467704/07/2024 08:52:48Closed34465103/07/2024 14:01:47In Development34461803/07/2024 09:09:58New34459802/07/2024 19:51:48New34459702/07/2024 19:48:17New 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!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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):

Cumulative chart.JPG

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!

Accepted answers

Stiltsoft support

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.

All comments

Stiltsoft support

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?

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:

Hope this helps.

Danut.

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:

Tue 3-1.png

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

Tue 3-2.png

Tue 3-3.png

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

Tue 3-4.png

Tue 3-5.png

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:

Tue 3-6.png

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets

chart-from-table