Weird Sorting Issue - Total Sales

imelendez

Hi, Experts, 

 

I created a domo card in table view for some top-level KPIs. I am running into an issue when I sort by Total Sales (calculated field/beast mode) by ascending you get the column sorted by 0 to the bottom. When you sort by descending it sorts by blanks.

 

Thoughts or help would be greatly appreciated.

GrantSmith

That's functioning as intended. NULLs always appear last. The actual data should appear after the nulls decending. What sort of behavior are you expecting?

 

If you want to override the sorting behavior you can utilize a separate beast mode to handle the nulls differently and sort based on this new field rather than the actual data field.

imelendez

@GrantSmith I have never attempted to do that. How would you do that? Can you provide an example how you would do a beast mode for the nulls as you mentioned?

ST_-Superman-_

You can update your calculated field to something like this:

 

case 
   when `Total Sales` is null then 0 
   else `Total Sales`
end

If you can share your `Total Sales` beast mode, I could give you a more concrete example

GrantSmith

@imelendez - 

 

@ST_-Superman-_ has it correct. It's a simple case statement to handle the nulls and replace it with 0.

 

Depending on your aggregate level you may need to wrap it in a SUM() (or do this on the sorting section of the card building interface)

imelendez

Here is the code - sorry for the delay.

 

SUM(CASE 
WHEN `rpt_job.authorized` = 1 
THEN (`rpt_job.subtotal_sales`) / 100
END)

Thanks for your help in advance @ST_-Superman-_ 

GrantSmith

@imelendez 

 

You should just need a simple tweak to your case statement to include an ELSE clause:

 

SUM(CASE 
WHEN `rpt_job.authorized` = 1 
THEN (`rpt_job.subtotal_sales`) / 100
ELSE 0
END)

DataMaven

@imelendez ... Didn't I harp about null values in our work together?  LOL

Domo does not handle nulls well.  Get rid of them in your ETL before the card.  Beast mode is fine in the interim, but you know I always want it firmly built in ETL if it can be.