Date order for cumulative total

[Dan Wilson]

Hi there, I am trying to set up a cumulative total for my pivot table (I am on version 6.14 and don't have the cumulative total option in pivot table).

I am using Jira data on the resolved column, and have set it up as follows:

I can get the date to pass through correctly for Table Filter and Pivot Table as below:

But when I apply the Table Transformer, the date format seems to be defaulting back to string, and applies the cumulative calculation that way as well:

Could someone help me with the correct code to be using?

[Katerina Kovriga _Stiltsoft_]

Hi @Dan Wilson ,

You may look how to do the cumulative count with the Table Transformer here.

Not sure what you mean that the date turns back to string (you don't necessarily need to have real dates in the first column - you may have "New day 1", "New day 2", etc. and the cumulative count for the second column will be working fine).

I've checked the behavior for the current version of the app that is 8.8.0. Strongly recommend to update. Besides lacking different features (for example, the cumulative count in the Pivot Table macro mentioned above), you don't have our new macros - check the Table Spreadsheet macro.

[Dan Wilson]

Hi there, testing if i can reply...

[Dan Wilson]

Hi there, hopefully this explains what I mean. This is the pivot of the data that I have, which I want to turn into a cumulative count:


However, when I follow the steps above using the table transformer, this is the result I get:

This is how my page looks in edit mode:

And my code from Table Transformer:

SELECT 'Resolved',
SUM (TT2.'Count') AS 'Count'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Resolved' >= TT2.'Resolved'
GROUP BY TT1.'Resolved'
ORDER BY TT1.'Resolved'

[Katerina Kovriga _Stiltsoft_]

So the sorting is messed up - check if the Date Format for your Table Transformer macro corresponds the current format.

And you may try to add another Table Transformer macro between the Pivot Table macro and the Table Transformer macro with a cumulative count. Just with a default SQL query and a proper date format.

If nothing helps, please contact our support team.

[Hardik Jani]

Hello,

I have following question. Can i also add future dates (for next 1 year) in this Resolved column and keep cumulative column same as last date and if the issues are added in future, it keeps the cumulative count updated?

[Stiltsoft support]

Hi @Hardik Jani ,

Not sure what you mean - in this example dates are coming from the Jira Issues macro (the Resolved field), so, I guess, they can't be set further in future.

When the Jira Issues macro starts to return more resolved issues as time goes, all our macros will be updated as well. We fully support dynamic Confluence macros.

If you want to create a table manually, you may use whatever dates you need. Just sort them correctly and apply the cumulative count.

[Hardik Jani]

Hello, 

Thank you for quick response. Yes, Data is coming from JIRA Macro. So There is no way i can create cumulative flow diagram with Table transformer macro with combining Created table and resolved table which can have overlap chart?

For example, 

Table above shows resolved date from June 1 what if created date is only until 5/1. Is there a way i can show cumulative which can overlap chart?

[Stiltsoft support]

Sorry, can't see the screenshot.

If you need to add some future dates manually, you can create an additional table, add them and merge this table with your Jira Issues macro.

Otherwise, the Table Transformer macro will work with the data coming from the Jira Issues macro only.