• 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.

Best way to present Yearly/Quarterly trend (data captured week on week) in graph

K

krishnabsharma

New Member
Hello Everyone!

I have 3 quarters data for 2011 and 1 quarter data for 2012 captured in week patern.

i want to analyze this whole data in one go just like a trend for all instances till now. can someone please help me out in acheiving the same?


i tried something which was very basic and completely manual in efforts.

Attempt i had given - i captured data weekly (for 2011 and 2012) and then took average @Month, Quarter and Year level. After this i did plot a bar graph showing trend over few instances.

Problems -

1. Complete manual effort

2. Not that much efficient

3. Could lead to complex calculation as data increases week on weeek and hence mistakes may occur


thanks in advance!


Thanks & Regards,

Krishna
 
Krishnabsharma


Firstly, Welcome to the Chandoo.org Forums


Well its really the wrong question, the question should be


What do you or your employer want to get from the data ?


Are you after trends in the data, year vs year, qtr totals vs qtr totals, running qtr vs running qtr amongst several other analysis I can think of.


Have you tried putting all the data into a Pivot Table/Chart

This is a great tool to quickly see what is going on as the data can be rearranged and grouped at will


Can you post your data with a few more ideas of what your trying to achieve?

refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Hui,

Thanks for your prompt response. i have uploaded the input file @link -


http://speedy.sh/EMkzA/Trend-Analysis-BaseData.xlsx


please have a look at the same and also i have metioned the problem statement in excel sheet only.


Thanks & Regards,

Krishna
 
Greetings there!

Can someone reply to my post please?


Problem statement -

Table contains the weekly captured data for particular products sale in that region. Currently I have considered only 2 regions data here but in realty I have more than 8 regions data for same number of weeks. With this excel tracking report, we try to plot the trend and analyze the sale for region/product.

please suggest how best i can present this data week-on-week, month-on-month, and quarter-on-quarter through both table and graph?


Input File - http://speedy.sh/EMkzA/Trend-Analysis-BaseData.xlsx


Thanks & Regards,

Krishna
 
Krishna


As I said in my previous response, the question really is "What story are you trying to tell with the data?"


You will most likely have Date on the X Axis

Then it is up to you as to how you arrange the other data to display it individually or cumulatively against the date


Setup the data to have fields for each item and the combined and the cummulative values for each field (Region and Product)


Then setup a chart and drag the chart series from data set to data set to see what it tells you


You could rearrange the data to put it into a pivot table/chart if you wanted also
 
You must log in or register to reply here.
Back
Top