Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

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

Vladimir Stankov February 20, 2024

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 areacluster
01-50-73testPUKSecurity
10-09-06test1PUKAccount
test2PUKAccount

 

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

1 answer

1 accepted

2 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2024

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 February 20, 2024

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 testand 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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2024

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 February 20, 2024

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

 

1.png

And here is the output:

2.png

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!

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events