Link to home
Start Free TrialLog in
Avatar of aturetsky
aturetsky

asked on

need a line chart of weekly app sales

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
Avatar of aturetsky
aturetsky

ASKER

disregard my previous comment - I meant to post it in my other question, so people will know to see this one
Avatar of Glenn Ray
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
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?
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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



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?
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?
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
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
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?
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:

https://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
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.
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).
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.
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
Glenn rocks!!!
Thanks for the recognition!  I was glad to help.
-Glenn