ETL: Calcs based on date ranges
I am using Magic ETL to load data. My data has columns for customer, month, product and revenue. I have created calculated columns in the ETL for the customers start month. I am trying to create a column that calculates the maximum monthly revenue for a customer within the first 3 months of their start date; this is dynamic so every customer has a different start date. I am using this for churn analysis later so want to put it in the ETL instead of in beast mode so that I can use it in other calculations. However, I am having a hard time figuring out how to calculate this as all the dates I see are fixed or relative but used in the entire data set. Any suggestions?
Best Answer
-
The SQL transform worked. I also was able to do this in the Magic ETL by creating another data flow and filtering for the range I was looking for and then doing a group by on that range.
0
Answers
-
Normally when doing something like this, I would use a SQL Transform. Here's what that might look like:
SELECT
a.*,
(SELECT SUM(`Price`) FROM dataset AS b
WHERE b.`Customer` = a.`Customer`
AND b.`Month` >= a.`Start Month`
AND b.`Month` <= DATE_ADD(a.`Start Month`, INTERVAL 2 MONTH)
ORDER BY SUM(`Price`) DESC
LIMIT 1) AS 'Max 3 Month Price'
FROM dataset AS aHopefully that logic will help get you started.
Sincerely,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
The SQL transform worked. I also was able to do this in the Magic ETL by creating another data flow and filtering for the range I was looking for and then doing a group by on that range.
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 203 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 181 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive