[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

need a line chart of weekly app sales

Posted on 2011-09-16
18
Medium Priority
?
261 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
  • 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

608 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