0

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

shazma
  • 55
  • 6

1 Answers1

2

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?

Zach J.
  • 366
  • 6
  • Thanks for your detailed response, I am now looking to bring back data on a daily level as you have described, think this will be the best way forward. Also new to alteryx and didnt know about the crosstab, so thanks for that suggestion too. – shazma Nov 07 '22 at 12:39