Joining the total SUM of two tables

Question

Hello Community!

I have two tables:

I am using a table filter macro to calculate SUM of hours in each of the columns: Downtime, Overtime and Working Hours.

I am trying to figure out if there's a way to join these two tables, so I can

a) see the total of Downtime, Overtime and Working Hours per Month in a single table;

b) use a formula & build a chart that would show what is the percentage of Downtime (60 hrs) out of the Total working hours (70 hrs) for the Month of October.

Thank you for your insights!

Elena

Natalie Paramonova _Stiltsoft_ · Answer

Hi @Elena Kondrashova ,

Thanks to @Luis Machado  - you have almost the whole case done!

To wrap everything, I can suggest the following structure:

The query for the internal Table Transformer macro is left as it is.

The query for the external Table Transformer macro is as following:

SELECT *,
'Working Hours' - 'Downtime' as 'Not Downtime'
FROM
(SELECT *
FROM T1
OUTER JOIN T*
ON T1.'Project' = T*.'Project' AND T1.'Month' = T*.'Month')

We’ll be using the new “Not Downtime” column later for our chart.

Here is the outcome of our macro:

The next step is to filter your table by month and count the required totals:

The last step is to build your chart:

Note that if you need to see the whole table with all the totals somewhere on the page, as @Luis Machado  has kindly mentioned before, use the Table Excerpt/Table Excerpt Include macros. For example, place your Table Excerpt between the external Table Transformer and Table Filter macros.

As your hosting is not specified, note that our screenshots were taken for Confluence Server/Data Center. For Cloud you’ll need to nest macros via the Table Toolbox.

Elena Kondrashova · Answer

Thank you @Luis Machado  really appreciate detailed response! I've tried to make suggested changes, and I think it worked. I do feel like the page takes time to load, so I will keep an eye on performance. Once again thanks a lot for your help.

Luis Machado_rllvhc · Answer

Hi Elena,

I see the problem. So because your rows have unique data, they will always have their own unique row in the table and the Lookup Table preset will match the duplicates. What you'd have to do is remove the unique values that cause the duplicates.

The Columns responsible for that currently are Date, Overtime, and Downtime.

Overtime and Downtime you could potentially work around by using the SUM() function to add the values from each row in a Month.

Date unfortunately is not something you'd be able to remedy, and it would need to be removed from the dataset to get the end result you want. If Date is required, I don't believe you'll be able to avoid some amount of duplicates.

So the first thing would be to wrap your table with the duplicate entries in a custom Table Transformer with the following SQL:

SELECT 'Project' as 'Project', 'Month', SUM(Overtime) as 'Overtime', SUM(Downtime) as 'Downtime'
FROM T1
GROUP BY 'Project', 'Month'
ORDER BY 'Project'

Which gives you the following result:

Then you nest that macro into another macro on the page that also has the second table in it. The query for the outer macro would look something like this:

SELECT *
FROM T1
OUTER JOIN T*
ON T1.'Project' = T*.'Project' AND T1.'Month' = T*.'Month'

In the edit view it looks like this:

and the end result looks like this:

If the dates are necessary, you could potentially have Table 1 live somewhere else and call it into your macro using the Table Excerpt and Table Excerpt Include macros. Then the table could exist with the dates in one view and without them in another so you don't have to duplicate keeping track of the same data.

Finally a word of caution, nesting macros like this can eventually lead to performance impacts depending on how much data you're running your calculations against. The more complex your build the higher the potential, so I would recommend keeping it simple where you can. Hope this helps!