ETL: Calcs based on date ranges

Theresa2000
Theresa2000
in Magic ETL

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

  • Theresa2000
    Theresa2000
    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.

Answers

  • Valiant
    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.

Categories