how to « Successful Software

Posts Tagged 'how to'

Pivot Tables demystified

Online businesses generate a lot of data. Sales data, marketing data, traffic data etc. Pivot tables can be a useful tool for analysing and extracting useful information from this sea of data. A lot of people seem to have heard of Pivot Tables without understanding what they are or how to use them. Despite the slightly cryptic name they are really just a way of summarizing tables of data. Nothing to be intimidated by. I’m going to try to demystify them here with a simple example.

Let’s imagine you have got an Excel spreadsheet with a month of (fictitious) sales transaction. It looks like this:

You want to find out:

The total value of sales of each product
The number of sales of each product
The total value of sales of each product by country
The total value of sales of each product by day of the week

How would you do it? You can crunch the numbers using a calculator, but that is very tedious and error prone. If you are a programmer you can export the data to a .csv file and write a small program in your favourite language to read it in, crunch the data and write the results out. You probably think you can do it in 10 minutes, but I bet it will take you at least an hour to get it working and debugged. I did all the above in 1 minute and 20 seconds using Excel pivot tables.

Here is how you can do it (screenshots from Excel 2007). You can download the spreadsheet if you want to try it yourself.

Select all the data, including the header (A1 to D222).

Click on Insert in the Excel Ribbon bar.

Click on PivotTable.

The Create Pivot Table window will appear. Click OK.

The PivotTable Field List will appear. We want to find the number of sales for each product. Drag the product field to Column labels and the value field to Values. This gives us the total value of sales by product.

To change this to the number of sales pull down the Sum of value drop-down list and select Value Field Settings….

Change Summarize value field by from Sum to Count and click OK.

The number of sales of each product are now shown.

Now drag the country field to Row labels. Then click on Count of value, select Value Field Settings…, change it back to Sum and click OK. The total value of sales of each product by country is now displayed.

We now need to add a day of the week column. Click on Sheet 1 and add a new column as shown.

Now select all the data from A1 to E222 and insert a new pivot table, as before.

Drag the fields as shown to get the total value of sales of each product by day of the week.

(We’ll quietly ignore the fact that the number of days aren’t divisible by 7 in the data.)

We can even display by product, day of the week and country with one more drag.

Excel also supports pivot charts if you prefer your results in graphical form.

Obviously this is a very simple example, but pivot tables can be used to quickly analyse much larger and more complex data sets. Next time you have got some data to analyse consider whether you would be better using pivot tables or pivot charts.

How to build an igloo

Published 13 January 2010 off topic 11 Comments
Tags: build, how to, igloo, snow

We have had loads of snow here in the UK. Loads by UK standards anyway (I don’t think a Scandinavian would be very impressed). So I decided to take full advantage of the flexibility my job allows and build an igloo. It was my second attempt and it turned out much better than rather wonky one I did a few days ago. This post is a quick overview of the modest amount I have learnt about igloo building, in case you are inspired to build your own.

First of all, building materials. The snow needs to be the sort you can squash together to make a snowball. If it is too powdery to stick together, forget it – you won’t be able to make a worthwhile igloo. Try again tomorrow.

Next you need to mark a circular base for your igloo. If you don’t then it is hard to get a decent overall shape. Two twigs and a bit a string is all you need to draw a circular outline. Don’t be too ambitous though, it takes a surpising amount of snow to build an igloo and the amount goes up fast as you increase the diameter. 1.5 to 2 metres diameter is plenty for a first attempt.

Then you need to have a bucket-shaped receptacles in a range of sizes. I used a household bucket as the largest, 2 different sizes of flower pot and a child’s bucket as the smallest. Start with the largest receptacle. Use it as a mould to create ‘snow bricks’. Pack the snow into the mould tightly to make strong bricks. Lay a circle of these bricks as close together as possible, leaving a gap for the door. Then place the next layer of bricks on top, interleaving them like standard brickwork. Pack the gaps between the bricks with loose snow like mortar in brickwork.

Every few layers you need to swap to a smaller mould. Each layer needs to curve inward a bit more than the previous one to form the dome. It is quite surprising how easy it is to build an arch out of snow. It is stronger and stickier than you might think.

It took 3 adults a couple of hours to complete the igloo. I don’t think the Inuit will be offering us a job anytime soon, but it was very satisfying. Considerably more satisfying than the several hours I spent this morning failing to work out how to get rid of a maximise icon in Mac OS X.

A YouTube video of a similar approach using stacking boxes