Produce weekly and quarterly stats from a monthly figure

I have a sample of a table as below:

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

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:

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

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:

Aggregate on your quarter to get a cleaner table

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?