Friday, September 2, 2016

Importing CSV/TSV data: SUM and AVG aggregate functions

These are accountant's favourite aggregate functions. Consider a sample data spreadsheet from Tableau slightly massaged to expose order year, quarter and weekday explicitly - here is the Google spreadsheet we will be working on. About 8000 order lines spanning a few years of an online superstore sales activity.

Quarterly sales by province
Let's create a Line chart with totals for each quarter by province (it happened to be a Canadian superstore).

1. Login to charte.ca and create a new Line chart.

2. In the Data panel, click "Import" (you can find it right under the data grid).

3. On wizard screen #1, paste data from the spreadsheet (we will re-use this data in all examples):


4. On Wizard screen #2, select series (Province), category(Year-Quarter) and value (Sales) columns:


5. On Wizard screen #3 (filtering), leave all fields blank - we do not need any filtering.

6. On wizard screen #4, select SUM aggregate function:


7. On Wizard screen #5 (sorting), leave default values in all fields and click "Done".

8. As a result, you will get this data grid and chart:




Quarterly sales by weekday
Let's see if there are any sales patterns that depend on the day of the week.

1. On Wizard screen #2, select series (Product category), category(Weekday) and value (Sales) columns:


2. On Wizard screen #3 (filtering), leave all fields blank - we do not need any filtering.

3. On wizard screen #4, again, select SUM aggregate function.

4. On Wizard screen #5 (sorting), leave default values in all fields and click "Done".

5. As a result, you will get this data grid and chart:

Average order line by shipment priority
Does the amount of order depend on the shipment priority? Do customers spend more money when they need some product immediately? Time to try AVG aggregate function.

1. On Wizard screen #2, select series (Ship mode), category(Year-quarter) and value (Sales) columns:


2. On Wizard screen #3 (filtering), leave all fields blank - we do not need any filtering.

3. On wizard screen #4, select AVG aggregate function:


4. On Wizard screen #5 (sorting), leave default values in all fields and click "Done".

5. As a result, you will get this data grid and chart:


Well, there are two spikes of Delivery Truck orders (furniture, maybe) in the first quarter sometimes, but I would not call it a trend, need more data.

No comments:

Post a Comment