Remove null values to make sorting works accordingly

Jez · November 20

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:

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.

ColemenWilson · November 20

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

ColemenWilson · November 20

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

Jez · November 20

Thanks a lot! :)

Remove null values to make sorting works accordingly

Best Answer

Answers

Categories