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

Arthi Annadi
Arthi Annadi Member
in Beast Mode

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?

Tagged:

Best Answers

  • marcel_luthi
    marcel_luthi Coach
    edited March 18 Answer ✓

    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
    Jones01 Contributor
    Answer ✓

    @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
    marcel_luthi Coach
    Answer ✓

    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
    marcel_luthi Coach
    Answer ✓

    @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)

Answers

  • Jones01
    Jones01 Contributor

    @art_in_sky can you post your beastmode please?

  • Arthi Annadi
    Arthi Annadi Member

    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
    marcel_luthi Coach
    edited March 18 Answer ✓

    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
    Jones01 Contributor
    Answer ✓

    @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
    Arthi Annadi Member

    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
    Arthi Annadi Member

    @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
    Arthi Annadi Member

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

  • Jones01
    Jones01 Contributor

    @art_in_sky

    case when IFNULL(col) then 'blank'

    when col = 0 then 'zero' end

  • Arthi Annadi
    Arthi Annadi Member

    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
    marcel_luthi Coach
    Answer ✓

    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
    Arthi Annadi Member

    Thank you for your response. It helped.

  • Arthi Annadi
    Arthi Annadi Member

    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
    marcel_luthi Coach
    Answer ✓

    @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)

Categories