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.

1 comment:

  1. This part is all these hanging video 카지노 games, which could be discovered on numerous units and have quite quantity of} betting options. The state handed laws in early 2020 to allow sports gambling at tribal casinos in-state. The state does not permit for mobile/online wagering.All betting on in-state collegiate games and occasions, including participant prop bets, is prohibited. All betting on in-state collegiate games and occasions, including participant prop bets, is prohibited. Super Slots is providing new players one of the greatest crypto casino bonuses going at present. When funding your account using crypto, enter the bonus code CRYPTO400 for a mammoth 400% deposit match as much as} $4,000.

    ReplyDelete