• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic charts with different criteria

K

kaushik03

Member
Hi All,


The sample workbook is here:


https://hotfile.com/dl/170723758/ed8c1d5/Dynamic_Chart_Data.xlsx.html


In attached workbook:


1) Col A contains list of unique Stakeholders

2) Col B represents total number of projects submitted by these Stakeholders

3) Col C represents total revenue generated from these Stakeholders


Now I want to prepare bar chart for these stakeholders as follows:


1) Top clients based on the number of projects count under the following breakup

a. For projects count >10

b. For projects count >=50

c. For projects count >=100


In the bar chart, X axis should have stakeholders and Y axis should have projects count.

There should be another series (for respective revenue) which should overlap with project count series. I mean revenue series bars should not be visible on the chart but only the data labels for revenue series should be reflected inside the count series bars.


Each one of the above criteria (a, b and c) should be marked with a radio button. Clicking on the buttons, respective charts should populate dynamically in the same place


2) Top clients based on the revenue

a. Revenue >10K

b. Revenue >=50K

c. Revenue >=100K


In the bar chart, X axis should have stakeholders and Y axis should have revenue amount.

There should be another series (for respective projects count) which should overlap with revenue series. I mean projects count bars should not be visible on the chart but only the data labels for projects count series should be reflected inside the revenue series bars.


Each one of the above criteria (a, b and c) should be marked with a radio button. Clicking on the buttons, respective charts should populate dynamically in the same place


I want to show these two charts within a small space, as much as possible (may be within (14RX19C……starting from B3)....something like this..


Hope I am able to make my points clear.


If you think any other chart types(instead of bar charts) would be much suitable in this scenario, please advise that as well.


Any help would be much appreciated.


Regards,

Kaushik
 
Hi


Please see this file is it appropirate?


http://dl.dropbox.com/u/60644346/Copy%20of%20Dynamic%20Chart%20Data.xlsx


Regards,
 
Hi Faseeh,


It is indeed a great technique that you have shown to slove this. I really like the approach of creating reference rows.


Couple of calrifications:


1)The range that you have set from J1 TO K3 might need to change.According to this, it is giving wrong count(at M4) and information and hence, the wrong chart.


Probably if we set the range as follows, it will give us correct result.


For More than 10K: 10000(at J1) 100000000 (at K1)

For More than 50K: 50001 (at J2) 100000000 (at K2)

For More than 100K:100001 (at J3) 100000000 (at K3)


(taking 10 crores as the extreme number)


2)As of now, changing the drop down gives us two different charts (based on the revenue break up).


However, for revenue breakup, I want both charts in one place( I mean both the revenue series and project count series)


Probably, we need to make the project count as the secondary axis with no fills for bar color and then reflect the data label for project count on the revenue series bars. But I am not very conversant with these techniques so require your expertise.


Same way I need for project count chart where revenue data labels ahould be reflected on the project count bars.


Please see my requirement in above post.


However, it's been a great help so far....you are amazing...


Looking forward to your response.


Kaushik
 
Hi Faseeh,


Another thing I could notice here, when I select > 10K from drop down total clients list coming up 74 which all are not reflected in the chart (it is showing 37).But when we stretch the chart, 74 clients are reflected together. So it is not accomodating within samall area.


So bar chart would at all serve the purpose? or we should think of some other chart type....what say?


Kaushik
 
Hi Faseeh,


I am able to achieve all my requirements entirely now (both the charts, series overlaping etc.).


Keeping your work as the base model, I am able to acieve my goals by doing some tweaking and necessary adjustments.


Thank you very much for your outstanding work and assist me on this.


Best regards,

Kaushik
 
Hi kaushik03,


You are always welcome and i am sorry for this much late reply, since there was night time when you were posting last two comments.


Regarding the chart showing all 74 clients, it has been easier if categories had been of same length, since we can then add a spin button to display only few at the moment, if we use that technique now then it will give problem for shorter range of clients.


I suggest you to explore dashboards by Chandoo, you will surely learn some great charting techniques.


Regards,

Faseeh
 
Hi Faseeh,


Thank you for your reply.


As suggested, I will surely look into Chandoo's dashboard articles.


I was also wondering if we can incorporate some horizontal scroll bar (or spin button as you suggested) to show say 10 clients at a time to make it visually more appealing.


Since you are already familiar with this technique, may I request you to please show me the same...how do we do it...


https://hotfile.com/dl/170850431/a598c8d/Dynamic_Chart_Data.xlsx.html


Thank you for all your help and valuable suggestions so far....


Regards,

Kaushik
 
Hi kaushik03,


I have tried so many times it gives problem for unequal ranges. means when we set max value of 72, it will show first 12 entries for the 3rd group and remaining will be blanks (as you keep scrolling)... hope you find a better solution.


Regards,
 
Not a problem Faseeh....


You have really done a great job because you have given me the right direction to approach to this problem.


I will also try at my own and will keep you posted about my progress. I am sure little more brain storming will give us the result (Insha Allah...)


Thank you dear...


Kaushik
 
Hi kaushik03, Faseeh

Here a proposition works if data are sorted descendent on Project Count (Sheet2)

In sheet3 the chart, sorting Project Count ASCENDENT needed to VLOOKUP and DropDown/ scrollbar to make the chart dynamic.

See formulas in Sheet3, Names added and some comments.


http://speedy.sh/w2aZf/Dynamic-Chart-Data.xlsx


Regards.
 
Hi Narayan,


The trick is cool!! One Question: When i shift from Range that has more entries to one that has less, the Chart shows N/A Error why is that?? Just asking out of interest. Can't we reset the Max Value of Spin Button through VBA??


@mercatog


That's a tricky one :) Very creative idea.


@kaushik03


I was trying to do something else but so far not successful. I will share the idea once i am done with that. So far, both Narayan's & mercatog's soultuion should suffice your need, and many thanks for your kind words. :)


Thank You,

Faseeh
 
Hi Faseeh ,


Thanks for pointing out the problem ; since I had chosen 5 as the band , and there was no case where the number of possible points was less than 5 , I did not take care of this situation.


To take care of this , all that is required , is to change the definitions of the 3 ranges :

[pre]
Code: 
X_axis          :   =OFFSET(Sheet3!$D$4,RangeLo-1,0,MIN(Sheet3!$A$1,RangeMax),1)

Variable_Range  :   =OFFSET(Sheet3!$E$4,RangeLo-1,0,MIN(Sheet3!$A$1,RangeMax),1)

Variable_2      :   =OFFSET(Sheet3!$F$4,RangeLo-1,0,MIN(Sheet3!$A$1,RangeMax),1)
[/pre]
Of course , the whole point of uploading the file was not to show off a fool-proof application , but a technique for getting what Kaushik wanted ! So please don't point out what will happen if you put a value of 0 or -1 in Sheet3!$A$1.


Narayan
 
Hi Narayan,


I had no bad intention in my mind when i made my last post, i am sorry if it hurt you. Actually i tried so many times if i can avoid n/a error that i mentioned i my second last post on this thread so i thought it should have been avoided when you used codes for that bcz in the end it VBA that does almost every thing, that you are expert in.


No offenses just a humble thought.


Faseeh
 
Hi Faseeh ,


No offence taken , no need to apologize. Like I said , it did not occur to me that this was a possibility.


All criticism is welcome.


Narayan
 
Hi Narayan,


Thank you very very much for such an outstanding work.It is exactly what I was looking for.


Further to Faseeh's question, I could see that you have suggested to change named range formula. Per your suggestion, I have incorporated the same in the workbook but the problem remains the same. Could you plz look into it again and provide a feasible solution for this?


I truly admit that the kind of work you have done is many years ahead of my present skills. May I request you to please explain your work a bit (VBA part) in order to help me understand / learn the techniques more clearly?


Your inherent ability to explain complex things in simpler fashion always helps me to learn things easily.


Mercatog: Your work also serve my purpose.Really, it's been a very creative thought...learnt a lot from you.As I said, your skills are many years ahead of me and hence it took some time for me to digest and understand your work.As a result, I reply you so late.


@Faseeh: I really give tons of thanks to you because of your outstanding initial work and drive this to become an ultimate success.


Looking forward to NARAYAN'S response.


Thank you all.


Regards,

Kaushik
 
Hi Narayan,


Problem is with appearing NA when we change the selection from drop down.


Consider the following situation:

I select 'more than 100k' and keep hitting the right spin button to get the last bar for 'Johnny,; and again I switch back to 'more than 50k' and do the same to get the last bar for 'Johnny'.


Now when I again select more than 100k from drop down I get #NA, after hitting the left spin bar for two times Johnny's bar is populated.I understand why it is happening.


[Sheet3!$D$4 is offseted by 14 rows down (RangeLo-1), row height is set as 5 (Sheet3!$A$1)....Hence, the formula ultimately fetching row 18 to row22 and giving #NA...clicking two times left spin button(named range set back to row4 to row 15), johnny' bar is populated back(VBA code does it)...please correct me if my understanding is wrong]


To correct this, you have suggested some changes in the named range formulae which I did but I am still getting #NA


Hope I am able to explain the problem. Please let me know if still you want me to upload the file.


Kaushik
 
Hi Kaushik , and Faseeh ,


Sorry if I misunderstood ; when I read Faseeh's comment , I opened the workbook , and saw what I thought Faseeh was referring to ; when the value in A1 is greater than the value of RangeMax ( M4 ) , then the X-axis would show all #N/A for the points A1 - RangeMax i.e. suppose A1 contained 10 ; this means the chart is supposed to display 10 data points. But if the total number of valid data points ( RangeMax ) is only 6 , then the X-axis would display the 6 valid categories , and then display #N/A for the remaining 4.


The fix I suggested was for this problem.


Now , I understand from your explanation that there is another problem. Download the revised file here ; the fix is again quite simple , to initialize the Spin Button through a module level procedure which is assigned to the list box.


http://speedy.sh/uKE4Z/Dynamic-Chart-Data.xlsm


Narayan
 
Yes Narayan....that is all....it's now working perfectly fine...thank you very much.


I requested one more thing to you to explain the code...it would be great if you could do that in your words plz...(hope I am not asking more Narayan...)


What more I can say to you....you are a true savior..


Best regards,

Kaushik
 
Hi Kaushik ,


Actually the code in this worksheet is not really so important ; the main work is done by defining the X-axis and the data series in terms of RangeLo.


The code is merely initializing the value of RangeLo when the sheet is activated or when the workbook is opened. Secondly , each time the Spin button is pressed Up or Down , the value of RangeLo is incremented or decremented.


The basic mechanism is a sliding window , whose width is the value input into A1 ; thus , if we start with a value of 1 for RangeLo , and the value in A1 is 5 , we display points 1 through 5 ; as the Spin button is pressed Up , we display , in turn , points 2 through 6 , 3 ... 7 , 4 ... 8 , 5 ... 9 , 6 ... 10 , 7 ... 11 and 8 through 12. Suppose RangeMax is 12 ; we stop at this stage , and any further pressing of the Spin button Up does nothing.


When the Spin button Down is pressed , we display similarly , till RangeLo comes down to 1 beyond which nothing happens.


Nothing more is required , and in this uploaded file , I have removed all references to RangeHi.


http://speedy.sh/3HjZQ/Dynamic-Chart-Data.xlsm


Narayan
 
You are awesome Narayan.


You have made things very simple for me now.


I have just tried to apply your technique in one of my other works and it worked. So, your efforts help me to learn new things...it's really amazing..


Hope to learn such many wonderful things from the treasure-box of your skills and knowledge.


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top