Remove null values to make sorting works accordingly

Options
Jez
Jez Member
edited July 7 in Calculations & Variables (Beast Mode)

Hello, I have a "transaction month" column that can't be sorted accordingly because I have some months with NULL values. When I used sorting ascending/descending my months looks like this:

Screenshot 2024-11-20 at 3.55.30 PM.png Screenshot 2024-11-20 at 3.55.00 PM.png

Question, is there a formula that can be used in beastmode or function in domo to correct my sequence by month when I have a NULL values?

Thank you.

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    You could correct the nulls by using the following beastmode:

    IFNULL(`YourValue`, 0)

    You can also sort any values using a beastmode like this:

    CASE 
WHEN `Transaction Month` = 'Jan' THEN 1 
WHEN `Transaction Month` = 'Feb' THEN 2 
WHEN `Transaction Month` = 'Mar' THEN 3
WHEN `Transaction Month` = 'Apr' THEN 4 
WHEN `Transaction Month` = 'May' THEN 5 
WHEN `Transaction Month` = 'Jun' THEN 6 
WHEN `Transaction Month` = 'Jul' THEN 7 
WHEN `Transaction Month` = 'Aug' THEN 8 
WHEN `Transaction Month` = 'Sep' THEN 9 
WHEN `Transaction Month` = 'Oct' THEN 10 
WHEN `Transaction Month` = 'Nov' THEN 11
WHEN `Transaction Month` = 'Dec' THEN 12
Else 13
END

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    You could correct the nulls by using the following beastmode:

    IFNULL(`YourValue`, 0)

    You can also sort any values using a beastmode like this:

    CASE 
WHEN `Transaction Month` = 'Jan' THEN 1 
WHEN `Transaction Month` = 'Feb' THEN 2 
WHEN `Transaction Month` = 'Mar' THEN 3
WHEN `Transaction Month` = 'Apr' THEN 4 
WHEN `Transaction Month` = 'May' THEN 5 
WHEN `Transaction Month` = 'Jun' THEN 6 
WHEN `Transaction Month` = 'Jul' THEN 7 
WHEN `Transaction Month` = 'Aug' THEN 8 
WHEN `Transaction Month` = 'Sep' THEN 9 
WHEN `Transaction Month` = 'Oct' THEN 10 
WHEN `Transaction Month` = 'Nov' THEN 11
WHEN `Transaction Month` = 'Dec' THEN 12
Else 13
END

    If I solved your problem, please select "yes" above

  • Jez
    Jez Member

    Thanks a lot! :)

Categories