Beast Mode Syntax error

Hi, I would greatly appreciate some help with this beastmode.

The idea is to calculate what % of the remaining sales quota for the quarter each open deal per salesperson is. I want to divide the Open deal value by (Quota - Value of all Won deals)

100000--this is the quota, manual input

case when `Status` = 'Open' then (`Value` / (100000 - SUM(case when `Status` = 'Won' then `Value` else 0))) else 0 end

Thanks in advance!

Find more posts tagged with

Accepted answers

ST_Superman

Try this

sum(case when `Status` = 'Open' then `Value` Else 0 end)

(100000 - SUM(case when `Status` = 'Won' then `Value` else 0 end))

All comments

ST_Superman

Try this

sum(case when `Status` = 'Open' then `Value` Else 0 end)

(100000 - SUM(case when `Status` = 'Won' then `Value` else 0 end))

n8isjack

And to be careful, though unlikely to happen, you can protect against "divide by zero" errors:

CASE 
  -- If denominator is zero, result is zero
  WHEN (100000 - SUM(CASE WHEN `Status` = 'Won' THEN `Value` ELSE 0 END)) = 0 THEN 0

  -- else, do the real calculation
  ELSE 
    SUM(CASE WHEN `Status` = 'Open' THEN `Value`) ELSE 0 END)
    /
    (100000 - SUM(CASE WHEN `Status` = 'Won' THEN `Value` ELSE 0 END))
END