Beast mode CASE calculation for number ranges that have null values in column.

Arthi Annadi

I have a column that has data type numeric and has numeric values and around 5 blank values.
I am trying to categorize the number to ranges 1-5, 5-10 and so on. The column has blank values as well. Well, when I am trying to implement the CASE calculation in beast mode, it throws me a calculation error. How do I correct this?. How do I handle " blank values" is it affecting my beast mode calculation?

marcel_luthi

Since CASE is always checked in order, you can simplify your beast mode to something like:

CASE 
WHEN COALESCE(`New_Column_Name`,0) = 0 THEN 'a) Zero or Blank'

WHEN `New_Column_Name` < 5 THEN 'b) Less than 5'
WHEN `New_Column_Name` <= 10 THEN 'c) 5-10'
WHEN `New_Column_Name` <= 20 THEN 'd) 11-20'
WHEN `New_Column_Name` <= 30 THEN 'e) 21-30'
WHEN `New_Column_Name` <= 50 THEN 'f) 31-50'
WHEN `New_Column_Name` <= 100 THEN 'g) 51-100'
ELSE 'h) More than 100'

END

The first one will take care of the blank values, keep in mind that this will work as long as your column is in fact a number column and not text. Let us know if this helps.

The reason I added letters is so that if you graph and you want to make them display in the given order you need to either do this, or have a separate beast mode returning an index to sort by (which won't work for Tables and Pivot Tables).

Jones01

@art_in_sky Try.

CASE
WHEN New_Column_Name < 5 THEN 'Less than 5'
WHEN New_Column_Name >= 5 AND New_Column_Name <= 10 THEN '5-10'
WHEN New_Column_Name >= 11 AND New_Column_Name <= 20 THEN '11-20'
WHEN New_Column_Name >= 21 AND New_Column_Name <= 30 THEN '21-30'
WHEN New_Column_Name >= 31 AND New_Column_Name <= 50 THEN '31-50'
WHEN New_Column_Name >= 51 AND New_Column_Name <= 100 THEN '51-100' ELSE 'More than 100'
END

marcel_luthi

The separation for Null should work, you can do it in several different ways, so your first statement would be:

CASE 

WHEN `New_Column_Name` IS NULL THEN 'a) Blank'

… 

And from there you cover all the other scenarios. Since case is always checked in order and stops after the first condition met is fulfilled, for example if you have a value of 47, it'll see see that it doesn't fulfill any condition until it reaches the < = 50 at which point it'll stop and return 30-50 as the output, not sure what you mean by duplicate values. Personally I don't trust BETWEEN as it has never really worked for me.

marcel_luthi

@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like:

CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END

Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at the Beast Mode level should you want)

Jones01

@art_in_sky can you post your beastmode please?

Arthi Annadi

Please find my beast mode calculation below
CASE
WHEN `New_Column_Name` < 5 THEN 'Less than 5'
WHEN `New_Column_Name` BETWEEN 5 AND 10 THEN '5-10'
WHEN `New_Column_Name` BETWEEN 11 AND 20 THEN '11-20'
WHEN `New_Column_Name` BETWEEN 21 AND 30 THEN '21-30'
WHEN `New_Column_Name` BETWEEN 31 AND 50 THEN '31-50'
WHEN `New_Column_Name` BETWEEN 51 AND 100 THEN '51-100' ELSE 'More than 100'
END

marcel_luthi

Since CASE is always checked in order, you can simplify your beast mode to something like:

CASE 
WHEN COALESCE(`New_Column_Name`,0) = 0 THEN 'a) Zero or Blank'

WHEN `New_Column_Name` < 5 THEN 'b) Less than 5'
WHEN `New_Column_Name` <= 10 THEN 'c) 5-10'
WHEN `New_Column_Name` <= 20 THEN 'd) 11-20'
WHEN `New_Column_Name` <= 30 THEN 'e) 21-30'
WHEN `New_Column_Name` <= 50 THEN 'f) 31-50'
WHEN `New_Column_Name` <= 100 THEN 'g) 51-100'
ELSE 'h) More than 100'

END

The first one will take care of the blank values, keep in mind that this will work as long as your column is in fact a number column and not text. Let us know if this helps.

The reason I added letters is so that if you graph and you want to make them display in the given order you need to either do this, or have a separate beast mode returning an index to sort by (which won't work for Tables and Pivot Tables).

Jones01

@art_in_sky Try.

CASE
WHEN New_Column_Name < 5 THEN 'Less than 5'
WHEN New_Column_Name >= 5 AND New_Column_Name <= 10 THEN '5-10'
WHEN New_Column_Name >= 11 AND New_Column_Name <= 20 THEN '11-20'
WHEN New_Column_Name >= 21 AND New_Column_Name <= 30 THEN '21-30'
WHEN New_Column_Name >= 31 AND New_Column_Name <= 50 THEN '31-50'
WHEN New_Column_Name >= 51 AND New_Column_Name <= 100 THEN '51-100' ELSE 'More than 100'
END

Arthi Annadi

Thank you Marcel and Jones for your answers. Both of the queries worked for me . I am curious to know why my CASE statement with BETWEEN didn't work and I believe they are in order, Aren't they?

Arthi Annadi

@marcel I think the less than operator kind of falsely categorizes the range values and include all possible duplicate values when indicated as less than equal to for each range. Do you Agree?

Arthi Annadi

Also what if I want to specify the blank and zero values as separate categories?

Jones01

@art_in_sky

case when IFNULL(col) then 'blank'

when col = 0 then 'zero' end

Arthi Annadi

Updating the beast mode gives me " issue request and doesnt display the graph", even though it says updated, when I go back to the beast mode calculation, it displays the old case statement.

Any inputs?

marcel_luthi

The separation for Null should work, you can do it in several different ways, so your first statement would be:

CASE 

WHEN `New_Column_Name` IS NULL THEN 'a) Blank'

… 

And from there you cover all the other scenarios. Since case is always checked in order and stops after the first condition met is fulfilled, for example if you have a value of 47, it'll see see that it doesn't fulfill any condition until it reaches the < = 50 at which point it'll stop and return 30-50 as the output, not sure what you mean by duplicate values. Personally I don't trust BETWEEN as it has never really worked for me.

Arthi Annadi

Thank you for your response. It helped.

Arthi Annadi

I have zeroes in a column which are leading to miscalculation of average. I want to create a beast mode calcualtion to avoid the zeroes in my calcualtion and calculate only the rows that have actual values other than zeroes. How do I achieve this?

marcel_luthi

@art_in_sky AVG works based on the number of non null entries, so if you null out your zeros that will solve for it. A beast mode like:

CASE WHEN `Column` = 0 THEN NULL ELSE `Column` END

Which then you'll select to aggregate using AVG should do the trick or removing the 0s from your AVG calculation (or you can aggregate at the Beast Mode level should you want)