Creating a graph using criteria from drop-down list

Question

Creating a graph using criteria from drop-down list

Hey all,

I am working with a large table containing indicators for countries; countries are listed in the first column and indicators are subsequent columns.

Since I have a lot of countries, it gets messy to display all countries on the same chart. So I created a column indicating each country's geographical region.

I would now like to have a graph that would display an indicator but only for the countries of a single geographical region I would choose from a drop-down list.

Is this possible? I haven't quite been able to find a way to do this so far and would very much appreciate a helping hand!

Thanks in advance!

~K.

Posted on Mar 18, 2018 3:50 AM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Mar 19, 2018 10:41 AM in response to kjhsdkhdkasda

Hi K,

OK, let's forget my first suggestion. A filter will do it. Here is the unfiltered table with all countries and regions on the chart. Your table is bigger, and the chart will be even more of a mess!

Now apply a filter from the menu on the column label B

To see the whole table again, use the Filter panel on the right of the window and delete the filter rule.

Regards,

Ian.

Reply

Answer 2

Yellowbox

Level 7

20,383 points

Mar 18, 2018 4:43 AM in response to kjhsdkhdkasda

Hi K'

Take a look at Template Chooser > Charting Basics > Interactive Chart.

Or, here is a way to create your own interactive chart:

The table "Weekly Data" is just data.

The table "Chart Data" has a Pop-Up Menu in cell A2, consisting of the data in "Weekly Data" Column A. Here I have chosen Week 1.

Formula in cell B2 of "Chart Data" (and Fill Right)

=INDEX(Weekly Data::B,MATCH($A2,Weekly Data::$A,0))

Select Rows 1 and 2 of "Chart Data" and insert a chart (graph).

Now choose another week (Week 3 in this example) in the Pop-Up menu and the formulas in "Chart Data" will "pull" the relevant data (row) from "Weekly Data" and automatically revise the chart.

Please call back if you need help adapting this to geographical regions. A screen shot of a small part of your large table will help us see what you see.

Regards,

Ian.

Reply

Answer 3

Mar 18, 2018 9:21 PM in response to Yellowbox

Hi Yellowbox!

Thanks for taking the time to answer this! Unfortunately, unless I am mistaken, there is a core difference with my example; let's see.

In the example you give, the pop-up element gives the week's number and, for each week number, there is only one week. That would be akin, in my case, to choose a country's name from a pop-up menu and display the indicators for that one country only.

In what I am trying to do, I select a region and display all countries from that region. But the number of countries per region varies. How can this difference be overcome?

Here is a screenshot of the beginning of my data set. Let me know if you need anything else.

Thanks a lot again for the help!

Best,

~K.