Hello, I have the following table and I am using the "Table excerpt include" macro and pulling data from other pages and visualize them here: Pageprojectkey areacluster01-50-73testPUKSecurity10-09-06test1PUKAccounttest2PUKAccount From this table I would like to create a chart by using "chart from table" macro and count the number of "key area" and "cluster" separately and to visualized it as a donut. I get the following results: This is correct, but I would like based on page 10-09-06 if possible to exclude the duplicates if any. So the expected result on the chart should be: PUK = 2 and Account = 1. I try using the pivot table macro and try different variations, but without success. If someone has experience with this I would appreciate some help and if possible to show me example how to do it. Thank you in advance! Best Regards, Vladimir Stankov

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; } } }

Chart from table remove duplicates to get correct values in the chart

Vladimir Stankov

Hello,

I have the following table and I am using the "Table excerpt include" macro and pulling data from other pages and visualize them here:

Page	project	key area	cluster
01-50-73	test	PUK	Security
10-09-06	test1	PUK	Account
10-09-06	test2	PUK	Account

From this table I would like to create a chart by using "chart from table" macro and count the number of "key area" and "cluster" separately and to visualized it as a donut. I get the following results:

2024-02-20 12_37_18-test23432432 - Business Connectivity - Confluence and 1 more page - Vladimir Sta.png

This is correct, but I would like based on page 10-09-06 if possible to exclude the duplicates if any. So the expected result on the chart should be:

PUK = 2 and Account = 1.

I try using the pivot table macro and try different variations, but without success. If someone has experience with this I would appreciate some help and if possible to show me example how to do it.

Thank you in advance!

Best Regards,

Vladimir Stankov

Accepted answers

Stiltsoft support

Hi @Vladimir Stankov ,

Sorry but I haven't got the request:

I would like based on page 10-09-06 if possible to exclude the duplicates if any. So the expected result on the chart should be:

PUK = 2 and Account = 1.

Do you mean that you want to exclude the duplicates coming from the "10-09-06" page?

But there are no any duplicates. You have 2 different rows:

test1/PUK/Account
test2/PUK/Account

All comments

Stiltsoft support

Hi @Vladimir Stankov ,

Sorry but I haven't got the request:

I would like based on page 10-09-06 if possible to exclude the duplicates if any. So the expected result on the chart should be:

PUK = 2 and Account = 1.

Do you mean that you want to exclude the duplicates coming from the "10-09-06" page?

But there are no any duplicates. You have 2 different rows:

test1/PUK/Account
test2/PUK/Account

Vladimir Stankov

Hello,

Thank you for the fast reply!

So let me try to explain in differently.

In this page 10-09-06 we can have several projects test1 test2... testn. The key area and the cluster will remain the same for all of the projects and will be duplicated each time. If I leave the field empty for test2 and use the "chart from table" macro I get an empty piece in the donut chart and I would like to avoid that.

What I want as an output is the following:

I would like to visualize the table as shown above and to say give me the key area and the cluster for page 10-09-06 if there are duplicates under key area and cluster in that specific page(there will always be once additional project is added) remove them and provide me the unique value only in the donut chart by using the chart from table" macro.

I hope that I gave you now more clear view of what is needed.

Best Regards,

Vladimir Stankov

Stiltsoft support

Then you may try the following workaround: place the Table Transformer macro between your mini tables that you collect and their Table Excerpt macros.

Tue 2-1.png

SELECT
DISTINCT 'key area', 'cluster'
FROM T*

Tue 2-2.png

Here we don't show the 'project' column (each cell has the unique contents, as I've understood) and remove the duplicates from the 'key area' and 'cluster' columns.

So, we transform your mini table into a one-row table and we collect this one-row table via the Table Excerpt/Table Excerpt Include macros.

Then for the result master table you'll be able to use the Pivot and Chart from Table macros as you've wanted firsthand.

Hope this workaround helps.

Vladimir Stankov

Hello,

Thank you very much! This resolved my issue. Since I have to show the project column I just made a copy of the "Table excerpt include" in "Table Transformer" macro

And here is the output:

The report and the charts are showing now correct data! The red rectangles shows that the data is duplicated, between the tables but this is not an issue.

Thank you very very much for the fast reply and for the help!

Welcome!

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

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-filter-charts-spreadsheets