Conditional selection of cells

Petar Todorov December 17, 2021

Hi,

For confidential purpose I have created a sample that represents my problem.

I have a table with Categories and Sub-categories, and 2 columns displaying current and target score.

I need to create a Radar chart with the Sub-Categories that have values in BOTH Current and Target Level. Radar chart has two aspects(Lines - for Current and for Target Level.

I need to create a Radar chart with the Categories with values representing the AVERAGE of the sum of each of Current level and Target level.

 

How can I select specific range of cells in confluence? Is it possible to interact with a particular cell using an ID (like in Excel)? 

@Katerina Kovriga {Stiltsoft} I have seen many of your responses. In this case I am using your plugin as well. Can you help?

Screenshot 2021-12-17 at 23.48.24.png

3 answers

4 votes
Katerina Kovriga {Stiltsoft}
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.
December 18, 2021

Hi @Petar Todorov ,

I can suggest the following workaround:

Sat 2-1.png

The last two rows in each table are used to filter empty cells if there are any.

Sat 2-2.png

Sat 2-3.png

Here we set the dropdown filters and choose their default values as "Empty" and set the reversed filters (so, we get "Not empty" cells).

Sat 3-1.png

Then we count average for the remaining not empty cells.

These operations are similar for each category table and you should repeat them.

Then we merge our result tables with the help of the Table Transformer macro:

Sat 2-4.png

Sat 2-5.png

Then we use another Table Filter macro and leave only "Average" cells.

Sat 2-6.png

Now we can build our chart - it seems to me that the Column chart is more suitable for the case:

Sat 2-7.png

The Radar chart is also possible but needs more data to look understandable:

Sat 2-8.png

Hope that this helps your case or gives an idea how to move on at least.

Petar Todorov December 18, 2021

Hi, @Katerina Kovriga {Stiltsoft} .

The workaround is fine, but is it possible to make it automatically. This table should be dynamic.

Also, the chart should use rows (the sub-category rows) and display 2 lines for current and target level. 

Thank you!

Katerina Kovriga {Stiltsoft}
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.
December 18, 2021

If you get the whole table automatically (through, for example, the Table from CSV or Jira Issues macro) and don't need to show average numbers on the chart, then you may try the following structure:

Sat 4-1.png

The first table is your macro and the second one is manually created (used for filtering of empty cells if you automatically generated table returns all the cells with numbers).

These two tables are merged with the help of the Table Transformer macro:

Sat 4-2.png

And then rows with empty cells are hidden with the help of the Table Filter macro:

Sat 4-3.png

As you can see, our Sub-category 5 has disappeared.

And then you can select sub-categories for your chart:

Sat 4-4.png

If further you need to calculate average numbers as you've mentioned in the original question, you may reuse the table after the Table Filter macro with the help of the Table Excerpt/Table Excerpt Include macros and wrap the reused filtered table into the Pivot Table macro:

Sat 4-5.png

Hope this will be somewhat suitable for the case.

Like # people like this
Petar Todorov December 18, 2021

@Katerina Kovriga {Stiltsoft} , a bit more clarification from my side:

 

Screenshot 2021-12-18 at 18.32.05.png

Screenshot is from an Excel chart. It is clear how to remove the empty Sub-category, THANK YOU for that. But can you show me how to make the chart like this? 

 

Thank you,

Peter

Katerina Kovriga {Stiltsoft}
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.
December 18, 2021

Please add another Table Transformer macro with a default SQL query (just insert it and don't change anything) between the Table Filter and the Chart from Table macros:

Sat 6-1.png

Then go to the Chart from Table macro and adjust the following settings:

Sat 6-2.png

Sat 6-3.png

Seems it looks similar to your screenshot.

Like # people like this
Katerina Kovriga {Stiltsoft}
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.
December 19, 2021

A little update from my side: pay attention that the Chart from Table macro comes after the Table Filter macro, so the rows with empty fields are already filtered and they won't be shown in the chart menu to add them for the values columns.

If these empty cells obtain any values, then you should add their sub-category to the chart manually.

The same happens if you have additional sub-categories generated - for example, sub-category 7, sub-category 8, etc. as for our example. You'll also need to ad them manually to the Chart from Table macro.

So, we can suggest to add all the possible sub-categories to the Chart from Table menu beforehand (just type them in into the required field). The sub-categories with empty cells and non-existing sub-categories won't be shown at the moment, but they will be automatically shown later if they obtain proper values.

We've also planned to add a "Select all" option for similar cases to select all available rows automatically, and I'll return to this thread once the feature is released.

Like # people like this
1 vote
Katerina Kovriga {Stiltsoft}
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 9, 2022

Hi there,

As this question mentions our Table Filter and Charts for Confluence add-on, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit modes.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

1 vote
Fabian Lim
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
December 17, 2021

Hi @Petar Todorov

Welcome to the community!

You may want to take a look their documenation to create radar charts: https://docs.stiltsoft.com/display/public/TFAC/Radar+%28Spider%29+chart+type

Or message their support team directly for a faster response.

Petar Todorov December 17, 2021

Hi, @Fabian Lim ! Yes, I have seen this. Forgot to mention it, apologies. The creation of the chart itself I do not think of much of a problem. However, in this documentation they do not mention how to select cells based on conditions. In general in the documentation I did not see a point where they are explaining how to used indexes to select/interact with specific cells. Let me elaborate:

 

Given the table above, if I want to create a chart with all Sub-Categories (Selecting this column to be viewed in the chart), then:

How will I EXCLUDE Sub-categories 4, 5 and 6, considering they do not have values for Current/Target. Also, if I add values to ANY of them, the sub-category needs to be included in the chart.

I have went through some of their documentation(not all) and I may have missed something. But I consider 2 options:

Using a LOOP to iterate over all Sub-category. If a sub-category has Empty value for current and target level - do not add it. However, I need the ability to use Cell Indexes here and I have not seen how can this be done in the documentation? Do you know a way?

Let's imagine the following implementation with Java:

for (int index=0; index<=subCategory.height; index++) {

       if (CurrentLevel[index] != null and TargetLevel[index] != null) {

             RadarChartArrayList.add(SubCategory[index];

       }

}

 

Second problem, when we talk about Radar chart from Categories:

I need to get all Categories whose Sub-categories have values for each of Current level and Target level. 

Is there any way I can get Sub-category1.parent and it will return me "Category 1"?

 

My plan is to:

1. Create a chart directly from my table, using conditions and selecting cells based on indexes? I need to use indexes - how?

2. Create a second table on which I will input only the sub-categories that have data for Current and target level. Again, I need indexes. How?

 

Thank you in advance

Like Fabian Lim likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events