• 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

aryan

Member
HI All I have a data in one sheet which includes process headcount average headcount closing headcount attrition % etc in column I want to make dynamic chart in another sheet for April 2013 till YTD dynamic if I select headcount it shows headcount graph also other after clicking it year to year comparison please suggest me how can I start step by step
 
Hi Sir,
What I need In first sheet data will be there.
In second sheet there are dynamic chart like if i want to click head count it shows only head count chart, if i click average head count, it shows average head count chart. like button type or any other option, where I can do comparison between Financial year April 2013 to YTD. it should be dynamic, more columns are still pending to add, year to year comparison between graphs and besides that required no of months i needed.
Thanks in advance
 

Attachments

  • graph.xlsx
    15.4 KB · Views: 1
Aryan

Of course I can do what you want but you will learn more by attempting this yourself. That is why we attach links to the posts we believe you can use as guides to learn from

See attached file
It use 3 Named Formula
 

Attachments

  • graph.xlsx
    18.5 KB · Views: 2
I have added a second chart
upload_2015-3-28_13-13-34.png

As you can see the possibilities are up to your imagination
 

Attachments

  • graph.xlsx
    20.8 KB · Views: 4
Sorry to intervene @Hui ...

@aryan - attrition is always an important metric for any organization..i would rather suggest to make a dashboard out of it to show different trends...like..gender diversity attrition, tenure attrition, YTD annualized attrition, 12M rolling attrition...etc etc

Just a suggestion..
 
Thats fine Asheesh

I only focus on the mechanics of the problems for areas I am unfamiliar with
 
Hi Sir,

Awesome , you saved me.
I was doing in a different way like I was making every column dynamic which I needed to show in chart and making separate dynamic chart like months, HC, AHC, CLOSING HC, and making separte columns for every department and repeating them, like months, HC, CHC, AHC ,was doing lot of work. and making separate chart for them and made around 18 charts. I read interactive dynamic chart. I was thinking to that. but right now I am doing the way you did . easy to do .
But you gave me second thought where I was unable to work.

I have some query like for Data_Set I want to make it dynamic because later i want to add some more columns. So How can I make their heading dynamic.

Why in the scattered plot months was missing in between? How can we show every months?
Second issue I was facing while doing comparison in 2nd chart when I was changing the chart areas then I was facing months problem . like Aprl'13 to Dec'13 and Jan'14 to Dec'14. so in column chart months was changed taking data from arpil 13 ' to dec'13 for both the series not in the scattered plots.

and what is this =Months14-365 in months14_offset.


Ashish - I also like your suggestion because i have to show different trends individual and comparison of different years which can come out from a data. If you have any ideas please share with me.
 
Hi Sir,

I want to know how can we make column heading dynamic like in my case it was (Data_Set).If i later add more column on that it should dynamic add.
and after changing the chart. It was either taking months from april'13 or Jan '14 .
In scatter plots why was gap between months.
 
Change the Named Formula:
Data_Set to: =OFFSET('Data sheet'!$C$3,,,1,COUNTA('Data sheet'!$3:$3)-1)
 
Please explain what "Missing in between" means?

There is no data between Jan and Mar 13
 
Hi Sir,

Thanks for the quick response and also thanks for enriching my knowledge.

Sir, as in the above 1st graph, it is a scatter graph. As we see in the first chart june, july was missing as it was showed in the no but not showing in the months, and so on.

same in the second graph. so the issue, like one or two months was not showing after june , july was not there. it is a technique or in scatter plot how can we show full months in x axis.

or I faced a issue like after I changed the graph i was facing the issue of months. I changed graph into clustered columns graph then either graph was taking months from april 13 or I have to select the months from Jan 14.Why it was happening in comparison graphs.

Thanks Sir in advance
 
Change the Chart Types to Line Chart instead of Scatter Chart
upload_2015-3-30_16-52-22.png

See attached file:
 

Attachments

  • graph2.xlsx
    21.5 KB · Views: 3
Hi Sir,

still facing the months problem. showing only 2013. months not showing 2014 months.2014 months is starting from Jan 2014, it is starting from 2014, but in x axis months is showing only 2013 months not even 2014 months.

if i changed may 2013 or JAN 2014 TO default May or Jan months then it will show year to year comparison. but for that after changing that my first graph will effect that will show only months not months in year.It means either I have to build another table for making year to year comparison or else.

Please suggest .


upload_2015-3-31_11-33-7.png


upload_2015-3-31_11-33-19.png


it is good for second graph where I am showing year to year comparison but my first which was showing months to months will convert to only jan to dec months.
 
But there is no data before April 13 ?

For the First chart change the Number Format for the X Axis to MMM YY
 
Select the Chart
Right click on the X Axis
Format Axis
Click on the Number Tab
Click on Custom
Untick linked to cell
In the Custom Format Box type MMM
Add
Apply
upload_2015-3-31_18-45-11.png
 
Hi Sir,

Thanks for your valuable time. I got it. I attached excel sheet. I have made a graph with yellow highlighted color with some remarks there.

Sir, If I add a third Named ranged like two previous months i.e. Months13, Months14, and now Months15, Because two years I have the data and I know these were my months in the year. But like third Months15, my data will grow months to months then name ranged Months15 will be static or how can i make it dynamic.


Or it will be ok to show 3 financial year comparison in a chart.
.
 

Attachments

  • hui graph 3rd.xlsx
    23.9 KB · Views: 2
The chart uses Named Formula to source the Data not direct linkages to the Cells as you have made
I have fixed that for the 2015 Year

Add a row of data and see the chart expand
 

Attachments

  • hui graph 3rd.xlsx
    25.1 KB · Views: 13
Back
Top