Using SUM and MAX

AttuAk
AttuAk Member
in Magic ETL

I am trying to get a quarterly view of a value where Q1 and Q2 are full quarters and Q3 is a year-to-date calculation.  So my table is showing Quarters as rows.  But when I try to pull the data using a beast mode like below, I get errors because I'm using the SUM aggregation with the MAX for the date (or the number of days).  Is there a way to do this where I can apply the MAX portion of this to only Q3 and thus not have the aggregation conflict?

 

sum(case when `ATTRIBUTE A` = 'Condition A' and `ATTRIBUTE B` = 'Condition B' then
case when `QUARTER` = 'Q1' then `Value`
when `QUARTER` = 'Q2' then `Value`
when `QUARTER` = 'Q3' then ((`Value`/90) * max(datediff(case when `ATTRIBUTE A` = 'Condition A' then `DATE` end,'2018-01-01')))
END
END )

Best Answer

  • AttuAk
    AttuAk Member
    Answer ✓

    Thank you for the response.  I wasn't able to insert a new attribute into our data table since I didn't have the time to do so.  Our table is a large Hadoop table.  

    But I did find another solution.  By taking the 'MAX' out of the conditional statement with the 'SUM' and then multiplying it when the `QUARTER` = 'Q3'.  I also needed to put the 'MAX' before the conditional rather than within it.  Like this:

     

    (sum(case when `ATTRIBUTE A` = 'Condition A' and `ATTRIBUTE B` = 'Condition B' then
    case when `QUARTER` = 'Q1' then `Value`
    when `QUARTER` = 'Q2' then `Value`
    when `QUARTER` = 'Q3' then (`Value`/90)
    END
    END )

    * max(case when `QUARTER` = 'Q3'

      then datediff(case when `ATTRIBUTE A` = 'Condition A' then `DATE` end,'2018-01-01')

    END))

     

    This worked perfectly for me.  

    Thanks for your response.

Answers

  • Valiant
    Valiant Coach

    For a similar calculation, I ended up adding a new row to my dataset and had it calculate the MAX applicable date for the range. 

     

    That way you can use the "MAX Date" almost as it's own variable in your beastMode. So if you added a `MaxDate` column to your data your new BeastMode would look like this:

    sum(case when `ATTRIBUTE A` = 'Condition A' and `ATTRIBUTE B` = 'Condition B' then
    case when `QUARTER` = 'Q1' then `Value`
    when `QUARTER` = 'Q2' then `Value`
    when `QUARTER` = 'Q3' then ((`Value`/90) * (datediff(`MaxDate`,'2018-01-01')))
    END
    END )

     

    Let me know if you need any assitance in builiding the new column and I'll be glad to give more detail.

     

    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

     

  • AttuAk
    AttuAk Member
    Answer ✓

    Thank you for the response.  I wasn't able to insert a new attribute into our data table since I didn't have the time to do so.  Our table is a large Hadoop table.  

    But I did find another solution.  By taking the 'MAX' out of the conditional statement with the 'SUM' and then multiplying it when the `QUARTER` = 'Q3'.  I also needed to put the 'MAX' before the conditional rather than within it.  Like this:

     

    (sum(case when `ATTRIBUTE A` = 'Condition A' and `ATTRIBUTE B` = 'Condition B' then
    case when `QUARTER` = 'Q1' then `Value`
    when `QUARTER` = 'Q2' then `Value`
    when `QUARTER` = 'Q3' then (`Value`/90)
    END
    END )

    * max(case when `QUARTER` = 'Q3'

      then datediff(case when `ATTRIBUTE A` = 'Condition A' then `DATE` end,'2018-01-01')

    END))

     

    This worked perfectly for me.  

    Thanks for your response.

  • AttuAk
    AttuAk Member

    ...or sorry, also needed to add the 'ELSE 1' at the end of the last statement so that the 'Q1' and 'Q2' values would be multiplied by 1.

Categories