ETL: Calcs based on date ranges

Theresa2000

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?

Theresa2000

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.

Valiant

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 a

Hopefully 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.

Theresa2000

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.