Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

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

Reply
Question marked as Solved
Answer:
Answer:

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!

User uploaded file

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

User uploaded file

User uploaded file

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


Regards,

Ian.

Posted on

Page content loaded

Mar 18, 2018 4:43 AM in response to kjhsdkhdkasda 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:

User uploaded file

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.

User uploaded file

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

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

User uploaded file

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.

User uploaded file

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.

Mar 18, 2018 4:43 AM

Reply Helpful

Mar 18, 2018 9:21 PM in response to Yellowbox 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.

User uploaded file

Mar 18, 2018 9:21 PM

Reply Helpful
Question marked as Solved

Mar 19, 2018 10:41 AM in response to kjhsdkhdkasda 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!

User uploaded file

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

User uploaded file

User uploaded file

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


Regards,

Ian.

Mar 19, 2018 10:41 AM

Reply Helpful (2)

Mar 19, 2018 4:13 AM in response to kjhsdkhdkasda In response to kjhsdkhdkasda

Hi K,


Another idea, closer to your original question.

Add another column (I inserted a column "Show?" as column C).

User uploaded file

B1 is a Pop-Up Menu created from all Regions

User uploaded file

Formula in C2 (and Fill Down)

=IF(B$1="Show all Regions","Show",IF(B2=B$1,"Show",""))

"Show all Regions" inserts "Show" into every row of C (to be used in a Filter rule to show the whole table).


Choose any Region from the Pop-Up Menu

User uploaded file

Now "Show" appears only in the rows for that Region.

Now add a Filter

User uploaded file

Only the rows containing Show are visible.

User uploaded file

You can hide column C.


Regards,

Ian.

Mar 19, 2018 4:13 AM

Reply Helpful
User profile for user: kjhsdkhdkasda

Question: Creating a graph using criteria from drop-down list