Hello everyone. Rob invited me to do a guest post to talk about my Wives vs. Girlfriends entry in the Alpha Geek Challenge. That is a politically incorrect and provocative title for a real data analysis mash-up that I did with PowerPivot.

I spent the last 33 days locked in a room finishing five manuscripts for books about Excel 2010. One of those books will be PowerPivot For the Data Analyst from QUE. In the course of writing that book, I had to generate a lot of sample data sets to use as examples.

Some interesting things along the way:
1) I had some daily sales data for 2 ladies boutique stores. One is in a mall. The other is in an airport located 10 miles away. 3 years of mall history, only 2+ years of airport history because that store opened after the mall location.

2) Beginning theory: when there are rain delays, people have more time in the airport, so they buy more.

3) Beginning theory: when it gets hot, people go to the mall.

4) I knew that Rob had found weather data from somewhere, but as I started searching around, I could not find any good free weather databases. I did find a page a WeatherUnderground.com where you could retrieve weather statistics for one day for one city. Luckily, both of these stores were in the same city, so I only had to gather data for one city.

Unfortunately, getting three years of data meant visiting this web site 1095 times! Excel VBA lets you use the brute force approach, so while I was typing a chapter on one laptop, I set up a VBA macro on the other laptop to run an Excel Web Query over and over and over, changing the date in the URL each time. As each web page would come back to Excel, I would use VLOOKUP to pluck out the rainfall and the high temperature for the day.

5) Getting the data into PowerPivot was easy. Two linked tables. One relationship by date.

6) In the PowerPivot grid, I added some calculated columns with DAX:

• To group the temperature data into buckets, I used a column called “Highs in the” and a DAX calculation of =INT(Weather[High Temp]/10)*10.
• To group the daily dates into weekdays, I used =FORMAT(Sales[Date],”ddd”). This returns Mon, Tue, Wed, etc. You have to use Colin’s trick from the FAQ to actually sort those into custom date sequence. (Thanks Colin!)
• Back in Excel, I had used the range version of VLOOKUP to assign classifications based on rain. The rainfall in inches was classified into “0-None”, “1-Trace”, on up to “4-Hurricane”.
• I did not realize it when I started, but as I was doing DAX Measures, I found the need to have some concatenated fields so that I could do a count distinct. =CONCATENATE(Sales[Location],Sales[Date]) and =concatenate(Sales[Location],Sales[WeekdayName]).

7) I used the “four chart” layout in PowerPivot. I ended up using only three of those charts in the contest entry.

8) The important Measure that was enabled by DAX was “Sales by store by day that falls into the current filter context”.  There were some days in 2007 where only one store was open, and many days where 2 stores were
open. DAX Measures let me calculate total sales / (# of stores open on this day). That DAX Measure is: =sum(Sales[Net Sales])/Countrows(DISTINCT(Sales[LocationDays]))

9) During discovery, I used slicers, but for the contest entry, I needed different charts to have different filters, so I changed the slicers back to report filters so that one chart could be for the airport and another chart could be for the mall.

Surprising results:
As rainfall increases, sales plummet the airport. I guess that when people are freaking about because of flight delays, they are not busy shopping.

Higher temperatures mean lower sales at the mall. When I was a kid, we would flock to the mall because they had air conditioning. Now, the people buying these high-end handbags probably already have air conditioning and no longer need to head to the mall.

Once I had reached some interesting conclusions about the data, I spent some time formatting the workbook to not look like Excel. I got rid of a lot of the extra stuff that is standard on Excel charts, but then added some graphics (the rain is a pattern fill on the chart area in the left graph and the Delay Delay Delay graphic is a picture fill applied to the plot area. To use pictures in individual columns of the other charts, you have to click on a column twice. Once to select the series, and then a second time to select that one point. Then, use Format, Fill, from Picture.

I suppose the surprising PowerPivot lesson here was that the slicers were really helpful to me when analyzing the data, looking for interesting trends. Once I had decided on the message that I wanted to convey, though, the slicers had to go.

Thanks to Rob for allowing this guest post!

## This Post Has 6 Comments

1. Michael says:

I’ve used that site for over a year to accumulate weather data, but I never considered using VBA to try to gather the data for me. I would love to know more about how you pulled that off. Thanks for the post.

2. Michael – thanks for the comment. I can post the full code this weekend when I get back to Ohio. However, I have this old article that shows how to use similar code to scrape data from many pages in a website. This will get you started.

http://www.mrexcel.com/tip072.shtml

Bill Jelen

3. General Ledger says:

Making SmartArt dynamic is excellent. I was very disappointed Microsoft didn’t make them this way to start.

I tried to duplicate your example of Creating the SmartArt using Excel 2007. I can not find Convert to Shapes. Is this only in 2010?

4. Yes – Convert to Shapes is new in Excel 2010. Here is how you do it in Excel 2007:

1) Click on some whitespace inside the SmartArt to activate the SmartArt.

2) Try clicking one one shape to select only that shape.

3) Press Ctrl+A to select all shapes. (You should see 4 selection handles on every single shape in the SmartArt.

4) Ctrl+C to Copy

5) Select a blank section of the worksheet. Ctrl+V to paste.

6) Delete the original SmartArt.

This is all a relative hassle, especially compared to how easy Convert to Shapes is in 2010. I am pretty sure that I was the first one to write about this method in my SmartArt book and I really suggested to the SmartArt team that there needed to be an easier way. It seems that the Convert to Shapes icon is their easier way. Not perfect, but definitely easier.

Bill

5. iqbal dhiman says:

i want to learn using of arrays of excelsheet in excel visual basic editor

6. Hi

I want learn Advance Excel & Macro VBA in Excel.

Plz provide me an send a email for everyday. to learn better Excel.

Thanks

&
Regards
Basavaraj K H
basavarajkh@ibibo.com
MIS Executive
Origin call Center
Bengaluru
Karnataka
India