Learning to Aggregate and Calculate Percentages
Hello and welcome back to our Wink Reports for Data Analysts course. In this lesson, we'll be building on the report request we've been working on and focusing on how to calculate the percentage each SKU makes up of the total sales. We'll start from scratch and create a new data source in a new folder called Project, and we'll be looking at the sales lines as it has the product and the SKU data we need to break things down. We'll add only the minimum data we need because the more columns we have, the more work the database has to do, which could affect performance.
To filter our report, we only want to see the sales for the previous year, so we don't want to show everything from 2016. To automatically select the previous calendar year whenever the report is run, we'll turn off the date filter and use a function to find today's date. We'll use this date to create a formula column and group our data by customer, date, and product SKU. This helps us to see the right data, but we don't want it per month. We want the total for the year so we'll remove the date column and just get the total. We'll change the name of the column to something that makes more sense once we've merged columns.
Next, we'll add a formula column to calculate the total quantity per customer. To do this, we'll use a window function. Wink supports a whole bunch of window functions, and we'll use the SUM function to sum the line quantity per customer name across everything. We'll have to be careful when using window functions and aggregations together as it can have unexpected results, but as experienced data analysts, we're not too worried.
Finally, we'll create a formula that calculates the percentage each SKU makes up of the total sales by dividing the total quantity per customer by the total quantity. This calculation is quite easy once we've summed up our rows.
That's it for this lesson! In the next lesson, we'll look at how to bring in the budget and calculate a forecast.
Topics with time stamps:
- Introduction (00:01-00:28)
- Report Request (00:28-01:03)
- Building the Report (01:03-02:20)
- Data Sources (02:20-04:08)
- Filtering Data (04:08-06:03)
- Making the Report Dynamic (06:03-11:48)
- Grouping and Aggregating Data (11:48-13:56)
- Creating a Formula Column (13:56-16:05)
- Using Window Functions (16:05-18:13)
- Dividing Columns (19:07-19:35)
Top learnings from this lesson:
- Building a report request from scratch requires careful planning and consideration of the data sources and variables to be included.
- It is important to keep the report simple and focused, and to only include the minimum columns and data that are needed to avoid performance issues.
- When working with date columns, it is important to filter the data to the specific time period of interest, and to consider ways to make this filter dynamic.
- Window functions can be a useful tool for summing and aggregating data across rows in a report.
- It is important to be aware of potential issues when combining window functions and aggregation in the same report, and to take steps to address these issues when they arise.
- Formula columns can be a powerful tool for performing calculations and combining data from different sources in a report.