Produce weekly and quarterly stats from a monthly figure

Question

I have a sample of a table as below:

Customer Ref	Bear Rate	Distance	Month	Revenue
ABA-IFNL-001	1000		01/01/2022	-135
ABA-IFNL-001	1000		01/02/2022	-135
ABA-IFNL-001	1000		01/03/2022	-135
ABA-IFNL-001	1000		01/04/2022	-135
ABA-IFNL-001	1000		01/05/2022	-135
ABA-IFNL-001	1000		01/06/2022	-135

I also have a sample of a calendar table as below:

Date	Year	Week	Quarter	WeekDay	Qtr Start	Qtr End	Week Day
04/11/2022	2022	45	4	Fri	30/09/2022	29/12/2022	1
05/11/2022	2022	45	4	Sat	30/09/2022	29/12/2022	2
06/11/2022	2022	45	4	Sun	30/09/2022	29/12/2022	3
07/11/2022	2022	45	4	Mon	30/09/2022	29/12/2022	4
08/11/2022	2022	45	4	Tue	30/09/2022	29/12/2022	5
09/11/2022	2022	45	4	Wed	30/09/2022	29/12/2022	6
10/11/2022	2022	45	4	Thu	30/09/2022	29/12/2022	7
11/11/2022	2022	46	4	Fri	30/09/2022	29/12/2022	1
12/11/2022	2022	46	4	Sat	30/09/2022	29/12/2022	2
13/11/2022	2022	46	4	Sun	30/09/2022	29/12/2022	3
14/11/2022	2022	46	4	Mon	30/09/2022	29/12/2022	4
15/11/2022	2022	46	4	Tue	30/09/2022	29/12/2022	5
16/11/2022	2022	46	4	Wed	30/09/2022	29/12/2022	6
17/11/2022	2022	46	4	Thu	30/09/2022	29/12/2022	7

How can I join/link the tables to report on revenue over weekly and quarterly periods using the calendar table? I can put into two tables if needed as an output eg:

Quarter Starting	31/12/2021	01/04/2022	01/07/2022	30/09/2022
Quarter	1	2	3	4
Revenue	500	400	540	540

Week Date Start	31/12/2021	07/01/2022	14/01/2022	21/01/2022
Week	41	42	43	44
Revenue	33.75	33.75	33.75	33.75

I am using alteryx for this but wouldnt mind explaination of possible logic in sql to apply it into the system Thanks

Answer 1

Before I get into the answer, you're going to have an issue regarding data integrity. All the revenue data is aggregated at a monthly level, where your quarters start and end on someday within the month.

For example - Q4 starts September 30th (Friday) and ends Dec. 29th (Thursday). You may have a day or two that bleeds from another month into the quarters which might throw off the data a bit (esp. if there's a large amount of revenue during the days that bleed into a quarter.

Additionally, your revenue is aggregated at a monthly level - unless you have more granular data (weekly, daily would be best), it doesn't make sense to do a weekly calculation since you'll probably just be dividing revenue by 4.

That being said - You'll want to use a cross tab feature in alteryx to get the data how you want it. But before you do that, we want to aggregate your data at a quarterly level first.

You can do this with an if statement or some other data cleansing tool (sorry, been a while since I used alteryx). Something like:

# Pseudo code - this won't actually work!

# For determining quarter
if (month) between (30/09/2022,29/12/2022) then 4

where you can derive the logic from your calendar table. Then once you have the quarter, you can join in the Quarter Start date based on your quarter calculation.

Now you have a nice clean table that might look something like this:

Month	Revenue	Quarter	Quarter Start Date
01/01/2022	-135	4	30/09/2022
01/01/2022	-135	4	30/09/2022

Aggregate on your quarter to get a cleaner table

Quarter Start Date	Quarter	revenue
30/09/2022	4	300

Then use cross tab, where you pivot on the Quarter start date.

For SQL, you'd be pivoting the data. Essentially, taking the value from a row of data, and converting it into a column. It will look a bit janky because the data is so customized, but here's a good question that goes over pivioting - Simple way to transpose columns and rows in SQL?