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: