Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Conditional selection of cells

Edited

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

2 answers

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.

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!

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

@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

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

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

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.

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
Community showcase
Published in Confluence Cloud

🏠 Say hello to the new Confluence Home!

Hi Atlassian Community, My name is DJ Chung, and I’m a Product Manager on the Confluence Cloud team. Today, I’m excited to share a new and improved version of Home. The new Home helps you ...

42,233 views 29 133
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you