What is the problem with my beastmode?

jasonm5545

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

david_cunningham

@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

jasonm5545

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

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

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

jasonm5545

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.

GrantSmith

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

jasonm5545

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