Beast Mode Syntax error

Options
user07000
user07000 Member
in Magic ETL

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!

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    Try this

     

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

    /

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

Answers

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    Try this

     

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

    /

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

  • n8isjack
    n8isjack Contributor

    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

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories