Solved: Getting % variation between 2021 and 2022 years - Page 2

LFicz · 10-18-2022 10:18 AM

Hey there people.

I have a dataset in big query and I need to calculate the % variation between 2 years (2021 and 2022). But my columns are very limited:

I have a column that shows the sales date, another one with the product value and another one with the supplier name.

(Sum all 2022 sales / sum all 2021 sales) -1 that is what I need to do and bring it back all results by supplier.

Is there a way to do that?

jbocachica

Hi, using a dataset from bigquery-public-data

This is a sample using 2019 and 2020 but is the same for your dataset, also, this query will help you in future years.

WITH fullresults as 
(SELECT EXTRACT(YEAR FROM date) AS year, sum(sale_dollars) total_sales, vendor_name 
 FROM bigquery-public-data.iowa_liquor_sales.sales
 where EXTRACT(YEAR FROM date) in (2019, 2020)
 group by EXTRACT(YEAR FROM date), vendor_name)

 SELECT f1.year previous_year, f2.year current_year,
 f1.total_sales sales_previous_year, f2.total_sales sales_current_year, 
 (f2.total_sales/f1.total_sales)-1 percentage, 
 f1.vendor_name
   FROM fullresults f1 
INNER JOIN 
fullresults f2 
on f1.year+1 = f2.year and f1.vendor_name = f2.vendor_name

Regards from Colombia

John

View solution in original post