Why is this not working

domoexpert
domoexpert Member
edited April 21 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')

Categories