Skip to content

    • Magic ETL

    Magic ETL

    Beast Mode for Sum Function

    Member
    in Magic ETL

    Hi All,

    I am using the sum function below which is throwing an error..can you please advise..


    CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN SUM(`CURRENT_INVENTORY`,`NONCURRENT_INVENTORY`,`OLD_INVENTORY`) END

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      SUM only takes a single argument. You'll need to add them together. Note that I'm adding 3 sums together as it will handle nulls otherwise if I add CURRENT_INVENTORY + NONCURRENT_INVENTORY + OLD_INVENTORY and if any one of those values is null then the entire value will be null causing incorrect summations.

      1. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `CURRENT_INVENTORY` ELSE 0 END)
      2. +
      3. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `NONCURRENT_INVENTORY` ELSE 0 END)
      4. +
      5. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `OLD_INVENTORY` ELSE 0 END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Answers

    • Coach
      Answer ✓

      SUM only takes a single argument. You'll need to add them together. Note that I'm adding 3 sums together as it will handle nulls otherwise if I add CURRENT_INVENTORY + NONCURRENT_INVENTORY + OLD_INVENTORY and if any one of those values is null then the entire value will be null causing incorrect summations.

      1. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `CURRENT_INVENTORY` ELSE 0 END)
      2. +
      3. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `NONCURRENT_INVENTORY` ELSE 0 END)
      4. +
      5. SUM(CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) THEN `OLD_INVENTORY` ELSE 0 END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach
      edited July 2022

      @ozarkram You cannot have multiple fields inside the same SUM() function. Try changing to this:

      1. CASE WHEN YEAR(`DATE`) = year(CURRENT_DATE()) 
      2. THEN `CURRENT_INVENTORY` + `NONCURRENT_INVENTORY` + `OLD_INVENTORY` 
      3. END


    • Member

      Thank you so much @GrantSmith and @MichelleH ..Really appreciate your help!

    • Domo Employee

      @ozarkram - I think that @GrantSmith 's approach is a better option here. He has included safeguards for any null values. If you choose @MichelleH's solution you will get a null value if any of the three fields are null.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories