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

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • @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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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-_ 

  • @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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.