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

Conditional selection of cells

Petar Todorov

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

Accepted answers

All comments

Fabian Lim

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

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

Katerina Kovriga {Stiltsoft}

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

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}

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.

Petar Todorov

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

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.

Welcome!

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

Quick Links

Find more posts tagged with

confluence