Solved

need a line chart of weekly app sales

Posted on 2011-09-16
18
251 Views
Last Modified: 2012-05-12
Based on the simple data in spreadsheet attached (names have been changed), I need to produce a line chart that shows how many times a given app was sold on a given week. purchases.xlsx
0
Comment
Question by:aturetsky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 1

Author Comment

by:aturetsky
ID: 36550378
disregard my previous comment - I meant to post it in my other question, so people will know to see this one
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36550430
Before I post a solution, can you clarify:
1) What day of the week does your business week start - Monday?
2) All your example data occurs within one business week, so the solution may not be obvious.

-Glenn
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:aturetsky
ID: 36550577
Yes, week starts on Monday.   As far more data, if you email me at [my username]@gmail.com, I'll send you a year worth of data.  Thanks!

Also, Glenn, I tried pasting my own data into your solution to previous question but it doesn't refresh the pivot chart properly, no matter what I do.  Any thoughts?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 36550687
Hi aturetsky,

I think I have a possible solution for you.
 Purchases-2.xlsx

I added a sheet to determine the sales week ("Calendar") and assumed that the business week starts on a Monday.  If this is different, you can change the first date (1/4/2011) and the rest will follow.

I randomly regenerated the purchase dates for this example.  I added a column to the original data that determines the week via a VLOOKUP function.  

I added two pivot tables.  The first is a classic table showing the games in rows, the weeks in columns and the quantity of sales in the data area.  The second is a pivot chart with the data transposed so that you can see a line chart for each game across weeks.  You can filter this to a specific game or games by clicking the drop filter in cell B1 and choosing those games to view (the chart will automatically update).

 pivot chart
note that both pivot tables are constrained to the current data range (1000 rows).  You will have to increase the reference size in both pivot tables if this changes (Menu: PivotTable Tools, Options, Change Data Source)

Hope that helps.
-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36550700
RE: updating pivot table results...my last comment above should address that issue as well.  You can avoid the possibility of missing data by making the referenced range significantly larger than the data area.  

For example, change the data source reference to:
Data!$A$1:$D$250000



0
 
LVL 1

Author Comment

by:aturetsky
ID: 36550892
Thanks.  Question - if I have a 100K+ rows, how do I get it to automatically fill in the week number in column D w/o having to hold now the mouse forever?
0
 
LVL 1

Author Comment

by:aturetsky
ID: 36551027
Glenn - thanks again for all your great help.  It's amazing how quickly you were able to produce this chart.  Another question - when I paste in my data, where I have 83 weeks worth of it, the column D says 52, for any week that's 52 and up, even though I properly updated the Calendar sheet.  Why would that be?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551577
1) Data range for Pivot Tables
Instead of a range of 250000 rows as shown previously, you may just want to enter the following:
Data!$A:$D
This will capture all data in the four columns, regardless of the size (up to the workbook limit of 1MB rows).  The downside is that (blank) values will show up in the pivot tables, but you can filter those out for clarity.

2) Copying formulas in a large range:
There is a very cool trick.  If you have a formula in a column that is adjacent to another column - completely filled with data - you can double-click the small square on the bottom right of the cell and it will automatically copy the formula to the bottom of the range, ending at the last row of the adjacent column. (see pic below)

There are other good keyboard shortcuts (ex. [Shift]+[End] plus direction arrows) which will help you jump to and select cells & ranges.  

3) Weeks beyond 52...
You need to change the cells referenced by the range name "Weeks" (which you can see in the VLOOKUP formula in column D).
Excel Menu:   Data, Name Manager
Select "Weeks" in the box below
Change the value in the "Refers To" box to include all the new rows of data. It currently shows
=Calendar!$A$1:$B$53
Change 53 to a higher number..make it 105 for now (two years of data if filled in).

Also, because you are looking at more than one year of data, the Weeks table lookup isn't ideal.  You would probably want to actually see the week starting date or week ending date instead.

Let me know if you'd like me to change that up and I'll attach another example.
quickcopy.JPG
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551746
Here is a modified version of your workbook.  It changes the reporting metric from "Week Number" to "Week Starting".  

In order for the Pivot Chart to properly display dates, you will have to ensure that the Pivot Table references ONLY the data range in the first sheet ("Data").  If you change it to my previous recommendation (Data!$A:$D), blank values for the date will prevent it from formatting properly.

So, here's what you'll have to do:
1) paste in your new data
2) copy the formula under "Week Starting" (again, use the trick I mentioned earlier)
3) For the two pivot tables, change the data source to select ALL the rows that you pasted and nothing more.

Hope that helps!
-Glenn Purchases-2.xlsx
0
 
LVL 1

Author Comment

by:aturetsky
ID: 36552252
Thanks for your wonderful help, Glenn.  Everything worked perfectly.  Let me ask you this.  If, instead of a line graph, I wanted to great a stacked bar chart so I can see how each app contributes to total sales for that week - how would I do that?  Or should I post that in a separate question?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36552363
aturetsky,

It appears that Glenn has given you a workable answer, so please, no points for this.

If you need the source range for a PivotTable to be dynamic, please have a look at this article, which explains a few ways to go about that:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

If you find the article useful, please click "Yes" in the "Was this helpful" voting buttons at the top of the article.

Patrick
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36553175
RE: changing the chart type.

 Very easy.  If you right-click anywhere in the chart area, you will see a shortcut menu pop up; one of the options is "Change Chart Type..."  (this is also available via Excel 2007 menu by clicking the chart, then selecting PivotChart Tools-Design, Change Chart Type)

Select the second Column chart option (Stacked Chart) then click OK.  It should display exactly as you are requesting.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36553177
By the way, matthewspatrick article is VERY GOOD and I recommend you read it in regards to creating dynamic ranges (I do usually use Lists in Excel 2003 or Tables in Excel 2007).
0
 
LVL 1

Author Comment

by:aturetsky
ID: 36556882
Thanks for your wonderful help, Glenn.  I am reading Mathew's article more carefully now.  Btw, why did you need both of the pivot charts (i.e. in the third and the fourth sheet).  They seem to be the same except switching the columns and the rows.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36560744
Hi aturetsky,

I just created both to give you a couple of different views of the data (i.e., transposing values).  You only need the one on the fourth sheet to give you the chart.

Glad I could help...I love charts - when they are done right! :-)

-Glenn
0
 
LVL 1

Author Closing Comment

by:aturetsky
ID: 36561096
Glenn rocks!!!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36561117
Thanks for the recognition!  I was glad to help.
-Glenn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question