Weird Sorting Issue - Total Sales

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.

Comments

  • GrantSmith
    GrantSmith Indiana 🔴

    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.

  • @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?

  • 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
    GrantSmith Indiana 🔴

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

  • 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
    GrantSmith Indiana 🔴

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

This discussion has been closed.