Is creating a compound line graph possible with this dataset?

May 31, 2012 2:59 AM

Is creating a compound line graph possible with this dataset? If yes, how? If not, how should I set up the data?

I have an excel table with data formatted like this:

Date, Country
01.01.2012, de
01.01.2012, us
03.01.2012, de
03.01.2012, de
04.01.2012, de
06.01.2012, uk
06.01.2012, ca
06.01.2012, de
06.01.2012, de
06.01.2012, us
06.01.2012, ch
08.01.2012, us
09.01.2012, de
09.01.2012, us
09.01.2012, us
09.01.2012, us

This represents the date on which a sale occurred and the country in which it occurred. Please note that the same dates often occur because there were multiple sales on this date.

I want to create a compound line graph with the date in the x-axis, and the number of sales that occurred on that date, compounded into what country the sales occurred.

Is this possible with the dataset and format that I have here or do I need to split it up, reformat it another way?

Would it be easier to graph the 2 values separately and somehow combine them together?

I've tried a few things like pivot and COUNTIF but my excel knowledge is a bit limited.

posted by chillmost to computers & internet (3 answers total)

Yes to pivottables. no to countif. Create a pivottable with your data, drag "Country" into the "column labels" area and "Date" into the "row labels" area. After that, drag "Date" into the main data area. Drag "Country" again into Values. Click PivotTable Tools/Options in the Ribbon, then click PivotChart, then click OK.

Edit chart to taste, move fields around to taste. This step will take many hours the first time you do it, because PivotCharts are really, really bad in Excel(but better than any alternatives).
posted by Yowser at 3:24 AM on May 31, 2012

Argh I hate Pivot Tables. I would just use formulae to seperate the data into lines for each Country. Then if you don't mind the data being spreadout across a larger date range then its pretty easy to sort it out. using two vectors one for country matches one for date matches then multiply entry wise.


dates... across the top, day by day.. so might be alot..
de
us
uk
ca
...

use matrix forumla (pressing Shift + Enter at end) to get the curly brackets ={SUM((column of country data) = country )*(Column of Dates = current Date))}


then you can easily chart these.
posted by mary8nne at 4:07 AM on May 31, 2012

Hey both of these will work for my immediate task at hand. Next step is making sure I get better structured data in the future. Thank you!
posted by chillmost at 8:09 AM on May 31, 2012