Why is this not working

domoexpert
domoexpert Member
edited April 2023 in Beast Mode

case when Sales/Volume = 'Sales' then case when YEAR(`Datestamp`) = YEAR(CURDATE()) -2 then CONCAT(ROUND(SUM(Sales_USD)/1000000, 0), 'M') END

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @domoexpert You are likely getting an error because of the second "case when" in your forumla. Case statements must follow this format:

    case 
        when ___ then ___
        when ___ then ___
        …
        else ___ — this is optional
    end
    

    I also recommend putting the case inside the sum and formatting functions. Here is what that should look like:

    concat(round(sum(case 
        when `Sales/Volume` = 'Sales' and YEAR(`Datestamp`) = YEAR(CURDATE()) - 2 
        then `Sales_USD` 
      else 0 end)/1000000,0),'M')
    

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @domoexpert You are likely getting an error because of the second "case when" in your forumla. Case statements must follow this format:

    case 
        when ___ then ___
        when ___ then ___
        …
        else ___ — this is optional
    end
    

    I also recommend putting the case inside the sum and formatting functions. Here is what that should look like:

    concat(round(sum(case 
        when `Sales/Volume` = 'Sales' and YEAR(`Datestamp`) = YEAR(CURDATE()) - 2 
        then `Sales_USD` 
      else 0 end)/1000000,0),'M')