Why is this not working

domoexpert

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

MichelleH

@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')

MichelleH

@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')