Selective Chart

glaupie

New Member
Joined
Sep 8, 2010
Messages
45
Hi,

I am doing a report on an Account Acquisition Campaign for my company. The Campaign consists if 12 "sub-Campaigns", which I want to report on separately and globally.

I have a sheet showing applications (approvals, declines, activation of approved accounts, etc.) per day.
I also have a sheet showing performance of these approved accounts (purchases, turnover, etc.) per day.

Currently it shows this per day, but only for the global campaign - I then have charts which pulls data from these sheets. I also have a main "summary" sheet (which is probably the only thing people look at, which shows the current situation (campaign start date, latest date, total approvals, activations, etc.), which for the most part also pulls data from the daily sheets, by loooking at the max value or sum of certain fields.

I pull the daily data from QlikView (so I just copy and paste to excel and everything updates itself).

My problem is that I want to somehow report on the 12 different campaigns without it looking messy. In QlikView I can add a dimension to my tables, so that for every day it shows the separate 12 campaigns (so each day will have 12 lines with all the different fields and people can filter on campaign name). This works nice for the summary sheet, because I can then globally show e.g. the approvals like this: =SUM('Application Stats by day'!E:E)
and for the different campaigns I can each have: =SUMIFS('Application Stats by day'!E:E,'Application Stats by day'!B:B,"=ACK SA: LB NEW. JUNE 2012")

My question is if I can do the same with charts - Can I have a chart pulling data with the date in Column A and the Approvals in Columns E, but only using the rows where Column B is equal to teh campaign name (e.g. ACK SA: LB NEW. JUNE 2012), and then shows the approvals per day for that one campaign?

I doubt it is possible but any ideas would be great. Sorry for the long post!


Gerhard
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Hi,

I am doing a report on an Account Acquisition Campaign for my company. The Campaign consists if 12 "sub-Campaigns", which I want to report on separately and globally.

I have a sheet showing applications (approvals, declines, activation of approved accounts, etc.) per day.
I also have a sheet showing performance of these approved accounts (purchases, turnover, etc.) per day.

Currently it shows this per day, but only for the global campaign - I then have charts which pulls data from these sheets. I also have a main "summary" sheet (which is probably the only thing people look at, which shows the current situation (campaign start date, latest date, total approvals, activations, etc.), which for the most part also pulls data from the daily sheets, by loooking at the max value or sum of certain fields.

I pull the daily data from QlikView (so I just copy and paste to excel and everything updates itself).

My problem is that I want to somehow report on the 12 different campaigns without it looking messy. In QlikView I can add a dimension to my tables, so that for every day it shows the separate 12 campaigns (so each day will have 12 lines with all the different fields and people can filter on campaign name). This works nice for the summary sheet, because I can then globally show e.g. the approvals like this: =SUM('Application Stats by day'!E:E)
and for the different campaigns I can each have: =SUMIFS('Application Stats by day'!E:E,'Application Stats by day'!B:B,"=ACK SA: LB NEW. JUNE 2012")

My question is if I can do the same with charts - Can I have a chart pulling data with the date in Column A and the Approvals in Columns E, but only using the rows where Column B is equal to teh campaign name (e.g. ACK SA: LB NEW. JUNE 2012), and then shows the approvals per day for that one campaign?

I doubt it is possible but any ideas would be great. Sorry for the long post!


Gerhard

There are many ways to do this, and here is a site with a bunch of examples:
http://chandoo.org/wp/tag/dynamic-charts/

For your specific case, there are several ways to do this:

1) Create a new range for just the data you want to show. Have a dropdown that will change what data is included in that range. Use named ranges to set the range of dates/data values. Have the chart use the named ranges instead of static ranges so that it will change when the data does
2) Create the chart based on a filtered list. When the filter changes, so will the chart
3) Create named ranges for each of the 12 series (you will need to create a macro or something to sort the data). Then have a name which refers to one of those series based on a dropdown or input cell. When the input cell/dropdown changes, the name will switch which series it points to. Have that name be the one the chart is made with.

Regardless, it will take some work to set up the sheet in the way which works best for you, so I suggest you look at the above link, see the sorts of things that are doable and how they are done, and take a stab at one that suits you. When you can't get further, try adding to this thread or making a new one.
 

glaupie

New Member
Joined
Sep 8, 2010
Messages
45
There are many ways to do this, and here is a site with a bunch of examples:
http://chandoo.org/wp/tag/dynamic-charts/

For your specific case, there are several ways to do this:

1) Create a new range for just the data you want to show. Have a dropdown that will change what data is included in that range. Use named ranges to set the range of dates/data values. Have the chart use the named ranges instead of static ranges so that it will change when the data does
2) Create the chart based on a filtered list. When the filter changes, so will the chart
3) Create named ranges for each of the 12 series (you will need to create a macro or something to sort the data). Then have a name which refers to one of those series based on a dropdown or input cell. When the input cell/dropdown changes, the name will switch which series it points to. Have that name be the one the chart is made with.

Regardless, it will take some work to set up the sheet in the way which works best for you, so I suggest you look at the above link, see the sorts of things that are doable and how they are done, and take a stab at one that suits you. When you can't get further, try adding to this thread or making a new one.

Okay I want to keep away from macros, so I went with Option 2. I now have my data source correct and on a separate sheet I have a chart. When I use the filter on the data sheet, the chart updates accordingly. But I want to make this easier for the user - is there any way I can have a dropdown list (or just a list table) of the 12 campaign names on the CHart Sheet - and when one or more is selected, it activates the filter on the data sheet...?

So on the Chart Sheet I select CAMPAIGN 1, and then on the data sheet the whole table filters as if I user the data filter at the top of the campaign name field.

I've tried finding ways to do this, but they all seem to require some VB work...

Thanks for the help
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Okay I want to keep away from macros, so I went with Option 2. I now have my data source correct and on a separate sheet I have a chart. When I use the filter on the data sheet, the chart updates accordingly. But I want to make this easier for the user - is there any way I can have a dropdown list (or just a list table) of the 12 campaign names on the CHart Sheet - and when one or more is selected, it activates the filter on the data sheet...?

So on the Chart Sheet I select CAMPAIGN 1, and then on the data sheet the whole table filters as if I user the data filter at the top of the campaign name field.

I've tried finding ways to do this, but they all seem to require some VB work...

Thanks for the help

Option 2) will require macros to do what you want (you can't filter that way unfortunately as far as I know).

Options 1) and 3) do not require macros, just formulas and named ranges -- they are a lot more complex to set up, but then can be controlled by non-macro controls on the sheet.
 

glaupie

New Member
Joined
Sep 8, 2010
Messages
45

ADVERTISEMENT

Option 2) will require macros to do what you want (you can't filter that way unfortunately as far as I know).

Options 1) and 3) do not require macros, just formulas and named ranges -- they are a lot more complex to set up, but then can be controlled by non-macro controls on the sheet.


Hmmm - okay, will you then maybe be able to asist me with Option 1 or 3? Whichever one you think is the better way to go.

Like I said, the to data sheets I update every day by pasting the updated information from QlikView (and doing one or 2 formulas for certain columns).

I can uplaod a file if that will help? I have no idea where to start.

Much appreciated.

G
 

glaupie

New Member
Joined
Sep 8, 2010
Messages
45

ADVERTISEMENT

Several charts (I think line charts will work the best). They will have between 1 and 3 series each, and they will all show information daily. For instance, one chart will show the number of applications received and approved, per day. And this chart I would like then to change depending on which of the 12 campaign names I select (and this selection box or dropdown list should be on or next to the chart). I will then have a chart right next to it showing the same information globally (for all 12 campaigns together).

In total I will have about 15 charts like this showing different information.
 

TKB

Board Regular
Joined
Aug 18, 2011
Messages
128
Well, if you want the user to be able to easily show/hide different companies you could always use an if statement.

Create a user interface section and highlight the cells in green and then place a "Y" in each of the cells (there should be the same number of cells as there are line items in the charts).

Create a data area that you can populate with all of the values being used in each of the charts. When you reference the cell with the value you want to use to populate the cell with, use the following formula:

=IF('Cell with Y' = "Y", Reference Cell in other sheet to populate this cell, 0)

What this will do is this, if the user inserts Y into the cells you set up as a user interface, the cell with the formula will reference the correct cell in your workbook to populate that cell. Otherwise, it will display a "0" if the user interface cell has anything other than a "Y" in it.

I hope that makes sense.
 

glaupie

New Member
Joined
Sep 8, 2010
Messages
45
Hi,

Sorry, that doesn't make much sense... my knowledge of excel is dangerous.

The thing is that I want to make no changes to the file on a daily basis - I will do this report every morning. I just update my QlikView file and copy and paste the entire table to excel. From this table my charts must update themselves.

I need the easiest wat possible for users to make selections on the CHART sheet that will cause the data to filter on the Data Sheet. In teh Data sheet I have a column named CHAMPION CHALLENGER DESC (which is the campaign name). I want all 12 of them listed on the chart sheet and it then filters the list so teh chart updates.

If this can only be done with VBA then I'll try - but I have never used it before.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Hi,

Sorry, that doesn't make much sense... my knowledge of excel is dangerous.

The thing is that I want to make no changes to the file on a daily basis - I will do this report every morning. I just update my QlikView file and copy and paste the entire table to excel. From this table my charts must update themselves.

I need the easiest wat possible for users to make selections on the CHART sheet that will cause the data to filter on the Data Sheet. In teh Data sheet I have a column named CHAMPION CHALLENGER DESC (which is the campaign name). I want all 12 of them listed on the chart sheet and it then filters the list so teh chart updates.

If this can only be done with VBA then I'll try - but I have never used it before.

Okay, attempt at simple way #3.

Assuming the amount of rows never changes, just make 12 charts on a new sheet (one to a cell, make the cell the size you want your charts).

Now name each cell something. For this example:

Campaign1
Campaign2
Campaign3
...
Campaign12

Now use the Camera Tool to take a picture of one of those cells (any one, it doesn't matter):
http://chandoo.org/wp/2008/12/02/excel-camera-tool-help/

Move the camera-taken picture to the Chart Sheet.

I'm going to refer to the camera-taken picture as Magic Chart from now on.

Now create a blank sheet. Call it "Lists" or something. In that, write the following:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Campaign Names</td><td style="font-weight: bold;;">Campaign ID</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Alpha Campaign</td><td style=";">Campaign1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Bravo Campaign</td><td style=";">Campaign2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Charlie Campaign</td><td style=";">Campaign3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Delta Campaign</td><td style=";">Campaign4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Echo Campaign</td><td style=";">Campaign5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Foxtrot Campaign</td><td style=";">Campaign6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Golf Campaign</td><td style=";">Campaign7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Hotel Campaign</td><td style=";">Campaign8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">India Campaign</td><td style=";">Campaign9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Juliet Campaign</td><td style=";">Campaign10</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Kilo Campaign</td><td style=";">Campaign11</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Lima Campaign</td><td style=";">Campaign12</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Change column A to whatever your campaign names really are.

Now create a new name called "CampaignList"
=Lists!$A$1:$A$12

And a name called "CampaignIDs"
=Lists!$B$1:$B$12

Now on the Chart Sheet, create a dropdown in $A$1 using data validation -> lists. For the list, hit F3 and pick "CampaignList"

In $B$1, add this formula:
=INDEX(CampaignIDs,MATCH($A$1,CampaignList,0),)

That should show you the campaign ID for the campaign selected.

Create a new name called CampaignSelector
=INDIRECT($B$1)

Click on the Magic Chart and it should say =Campaign1

We'll change that to =CampaignSelector

Now when you change the dropdown in $A$1, it will change the ID of the name in cell B1, which will change where the Magic Chart points to.

If your data changes in amount, you can automatically modify the length of the series using a name with OFFSET:
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

That will automatically take all the data in a column and put it in a name. Use the names as your series references.

I hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top