Solved

need a line chart of weekly app sales

Posted on 2011-09-16
18
246 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: 36550372
0
 
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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 92

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now