Cumulative calculated columns in a Table Transformer

Question

Hi,

If I have a table which has the following format:

DateStatus01/01/2024Pass02/01/2024Fail30/01/2024Fail

How do I transform it so I can calculate a cumulative count and percentage of failures, at a per row level, as follows? I would then want to use a line graph to model the failure rate over time.

DateStatusTotalAttemptsCumulativeFailuresFailureRate01/01/2024Pass100.0002/01/2024Fail2150.0030/01/2024Fail3266.67

I tried some fairly complicated SQL in the Table Transformer, but cannot get it to output the calculated values per row.

Stiltsoft support · Answer

The support portal isn't strictly about bugs or smth - we help with use cases there as well. The advantage is that all the tickets are registered there and we don't find them manually, we see your history and sometimes it helps to understand all the set up.

Besides, it's confidential, so we'll be able to exchange screenshots (of your original tables and, for example, page trees if we need excerpts or smth). Also if you have big queries that work not as expected, you can send us your page storage and we'll be able to recreate exactly your macros (with all the settings and the current query), make some changes and send it back - it helps a lot.

The Community is more about sharing general experience. The leaders often help with basic questions regarding Confluence, Jira and other standard products but not with 3rd party apps especially when it involves SQL.

Green, Desmond · Answer

Ok, thanks. Not sure why I was referred to the support portal, as it is not a problem at all with the product, only a logical problem I was trying to solve, of the like I have previously had answers to from the experts within this forum.

The documentation you also provided did give me a good idea on how to solve the problem, and so I prompted ChatGPT to use that approach (based on my previous failed attempts), it gave me this, which works perfectly!

SELECT
T1.Date,
T1.Status,
COUNT(T2.Date) AS TotalAttempts,
SUM(CASE WHEN T2.Status = "Fail" THEN 1 ELSE 0 END) AS CumulativeFailures,
ROUND(
100.0 * SUM(CASE WHEN T2.Status = "Fail" THEN 1 ELSE 0 END) /
COUNT(T2.Date),
2
) AS FailureRate
FROM
T1
JOIN
T1 AS T2
ON
T2.Date <= T1.Date
GROUP BY
T1.Date, T1.Status
ORDER BY
T1.Date;

Stiltsoft support · Answer

Hi @Green, Desmond ,

As your question is related to the Table Transformer macro, it seems that you have our Table Filter, Charts & Spreadsheets for Confluence app.

So, please feel free to refer to our support portal and raise a support ticket there.

Meanwhile, here is our documentation how to calculate running totals using the Table Transformer macro.

As you need to count strings, it seems that you need to assign indexes for your statuses beforehand.

For example, pass-fail-fail as attempts will look as 1-1-1 and as failures - 0-1-1. Then you'll be able to count both columns cumulatively. And the third step will be to count the required percentage.

Or maybe we'll be able to simplify the solution during further discussion via the support portal.