Dynamic Charts

[HangMan]

I have a large data set and I would like to be able to produce a dynamic chart which switches data sets based on three different sets of criteria.

1. By Continent, Country and Town. I would like to be able to display a 'Radio Button' (or similar) for each, so that when the user clicks on one, the data is dispalyed accordingly by Continent, Country or Town.
2. I want to include a drop down list to display a list of different graphs that can be dispalyed, e.g. Sales, Cost of Sales, Gross Profit, Net Profit etc.
3. I want to allow the user to make comparisons within the data, e.g. to compare say three contients or compare 12 countries or 20 towns in the same country. To do this would require the user to be able to click a check box against the Contients, Country or Towns they want to compare.

Although I can figure out how to make a basic Dynamic Chart, I really don't know where to start (despite having spent many hours searching) on implementing the other 'Options'. I would like the user to be able select any of the graph options from the drop down menu and then click a radio button to display this data by either Continent, Country or Town and also give them the option of adding or subtracting Continents, Countries or Towns from the data set by clicking or unclicking checkboxes.

I would be grateful for any pointers, examples or suggestions as to how to go about achieving this.

[lmvk99]

Are you sure you can't do this the easiest with a pivot chart?

If not, can you give me an example of a couple of records in your database to work with (just put dummy data in if necessary).

[HangMan]

Well, I'm reasonably open to suggestion, though the idea is to incorporate this as a sort of 'Front End' to a large data set.

I'm just putting some dummy data together now and I'll upload this shortly to try and better explain what I'd like to achieve (the source data is sensitive).

Many thanks

[HangMan]

Hi Imvk99

I spent yesterday trying to find ways to achieve what I'm looking for. I looked at 'Pivot Charts', 'Filtered Tables', 'Data Validation Menus' and a whole host of other things, but nothing gives me everythnig that I need. Each option will give some of the answers but none give all of them.

I've attached a spreadsheet with some 'dummy data' on it... The actual data set is very large but it is the approach that I'm strugglong with.

What I would like to be able to do with the attached is to allow the user to show the following as graphs, ideally by being able to to click checkboxes to make comparisons or radio buttons to change the data set, or something along those lines. I'm open to suggestion as to how this could work. The approaches I've looked at so far, allow maybe one or two options but not all of them.

1. Av Monthly Units Sold by either Continent, Country or Company
2. Av Monthly Turnover 2010/2011 by either Continent, Country or Company
3. Av Monthly Turnover 2011/2012 by either Continent, Country or Company
4. Av Monthly Overheads 2010/2011 by either Continent, Country or Company
5. Av Monthly Overheads 2011/2012 by either Continent, Country or Company

I'd also like to be able to make comparisons, for example

1. Compare the Av Monthly Untis Sold in 2010/2011 with 2011/2012 by either Continent, Country or Company
2. Compare the Av Monthly Untis Sold between two, three, four or any number of different Continents, Countries or Companies by being able to select the Continents, Countries or Companies to compare
3. Compare any data set by year and by any number of Continents, Countries or Companies

Hopefully this makes sense. I would be grateful for any pointers, help or examples.

[jpr73]

Hi,
May be a solution by using pivot table and pivot chart
See attached the result for the first approach
To make comparison you need to have a table with corresponding data :

- Av Monthly Units Sold in 2010/2011 with 2011/2012 ?

- any data set by year ?

Note : in the Data table the headers for Turnover and Overhead are not correct
Hope this help
Best regards