Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# need a line chart of weekly app sales

Posted on 2011-09-16
Medium Priority
257 Views
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
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
• 9
• 8

LVL 1

Author Comment

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

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

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

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

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

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

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

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

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)

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

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

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

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

Patrick
0

LVL 27

Expert Comment

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

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

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

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

ID: 36561096
Glenn rocks!!!
0

LVL 27

Expert Comment

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

## Featured Post

Question has a verified solution.

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

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month6 days, 12 hours left to enroll