What is the problem with my beastmode?

jasonm5545
jasonm5545 Member
edited May 8 in Connectors

I am trying to make an outlier flag that flags values 50% above of 50% below the budgeted, or daily goal mount. I made two threshold calculations(dailygoal / 2) & (dailygoal * 1.5). I keep getting an error message, but I am not sure what I am doing wrong.

Any suggestions?

Thanks, and my sql command is attached below.

CASE WHEN
Donors
BETWEEN
Lower Budget Threshold AND Upper Budget Threshold
THEN 'Normal'
ELSE
'Outlier'
END

Tagged:

Answers

  • david_cunningham
    david_cunningham Coach
    edited May 8

    @jasonm5545 I just commented on your other post that looked like the same question. Posting here as well so you have it.

    The issue is the argument BETWEEN. Try this instead.

    CASE   
  WHEN Donors >= Lower Budget Threshold OR Donors < Upper Budget Threshold  
    THEN 'Normal'  
  ELSE 'Outlier'
END

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • jasonm5545
    jasonm5545 Member

    Thanks! It helped solve the error but brought me to a different question. I changed the and to an or, and there is no error, but the outlier flag option does not even show in the filter pane. Any idea why?

  • jasonm5545
    jasonm5545 Member

    This is my new sql command. For some reason, it is flagging everything as 'Normal', even with values clearly outside of the range.

    CASE WHEN

    Donors < Lower Budget Threshold OR Donors > Upper Budget Threshold
    THEN 'Outlier'

    ELSE 'Normal'
    END

  • david_cunningham
    david_cunningham Coach

    Is Donors a numeric field and is it being stored as such? Are you aggregating any of your fields?

    Please post some screenshots of your dataset and output so that I can try to figure out what's going on.

    For example - you can see below that the logic is working as intended.

    case when value < lower_bound or value > upper_bound then 'Outlier' else 'Normal' end

    Yields the following result

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • jasonm5545
    jasonm5545 Member

    Donors is a numeric field, and so is both of the thresholds.

    For example, this last column should be flaggeed, but is not. Every column is showing normal, and outlier is not even an option in the filter pane.

    image.png
    image.png

  • GrantSmith
    GrantSmith Coach

    You're deafaulting to Normal if the Donors is null. You can add an additional clause in your case statement to handle NULL values:

    case when value < lower_bound or value > upper_bound or value is null then 'Outlier' else 'Normal' end

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jasonm5545
    jasonm5545 Member

    Thanks. When doing this, all it did was change the default to outlier. The flag is not picking up on both conditions.

Categories